一直以來都在Select Join 時，如果需要篩選都毫無疑問的直接寫在Where裡面。
但今天才明白到原來可以在 join 時就加入條件
select HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate) as orderyear,COUNT(SO.EmployeeID) as cnt
from HR.Employees HE
inner join Sales.Orders SO
on SO.EmployeeID = HE.EmployeeID
group by HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate)
order by EmployeeID,YEAR(SO.OrderDate)
以這個例子舉例給大家看
如果今天我們想要只Select 出 OrderDate為 2006 的資料
我之前就會直覺覺得就是要寫下Where 條件
select HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate) as orderyear,COUNT(SO.EmployeeID) as cnt
from HR.Employees HE
inner join Sales.Orders SO
on SO.EmployeeID = HE.EmployeeID
where YEAR(SO.OrderDate) = '2006'
group by HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate)
order by EmployeeID,YEAR(SO.OrderDate)
但其實這是透過Join 後，再Where 效能上其實是比較消耗的
那可不可以原本Join 的表，就先篩選過才進行Join呢?
那就是
select HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate) as orderyear,COUNT(SO.EmployeeID) as cnt
from HR.Employees HE
inner join Sales.Orders SO
on SO.EmployeeID = HE.EmployeeID
and YEAR(SO.OrderDate) = '2006'
group by HE.EmployeeID,HE.FirstName,HE.LastName,YEAR(SO.OrderDate)
order by EmployeeID,YEAR(SO.OrderDate)
結論:
這樣的方式就可以Join Orders 這張表時 ，給的就是 OrderDate等於2006 的資料
而不是 加入Where 條件時，先Join後才篩選條件