Robert Dudley (5/21/2010)

---

traughberk (5/21/2010)

---

I have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.

SELECT C.CustomerID

FROM Sales.Customer AS C

LEFT JOIN Sales.SalesOrderHeader AS OH

ON C.CustomerID = OH.CustomerID

AND OrderDate>='2004-07-01'

WHERE OH.CustomerID IS NULL

AND C.TerritoryID=10

Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.

Thanks.

Kris

Kris - the "AND OrderDate >= '2004-07-01'" is filtering under the JOIN function. When you JOIN two tables you define how the two relate to each other. In the example, the "C" table links to the "OH" table through the CustomerID field in each table being equal. You could state that they be different, in which case you link each record in table "C" to all other recrods in table "OH" where the CustomerID's are different. Why you would want to do this is beyond me. I am just illustrating some functionality. So the second parameter in the JOIN linking definition is that on top of the fact that the CustomerID's in table "C" and table "OH" have to be the same, the Order Date must also be greater than or equal to July 1st, 2004. An alternative is to drop the AND statement from the JOIN function, and apply it in the WHERE clause. One way may be more efficient than the other.