Left Join

As promised here is a left join blog post. What is a left (outer) join? This is what Books On Line has as the left join description “The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.”
So let’s see how that works

CREATE TABLE #Customer
(CustomerID INT ,
LastName VARCHAR(100),
FirstName VARCHAR(100))

CREATE TABLE #Order
(OrderID INT identity ,
Amount DECIMAL(12,3),
CustomerID INT)

INSERT INTO #Customer VALUES (1,'Gates','Bill')
INSERT INTO #Customer VALUES (2,'Woz','Steve')
INSERT INTO #Customer VALUES (3,'Ellison','Larry')

INSERT INTO #Order VALUES (12.99,1)
INSERT INTO #Order VALUES (13.45,1)
INSERT INTO #Order VALUES (56.45,3)

--Regular Join, CustomerID 2 is not returned
SELECT c.*,o.*
FROM #Customer c
JOIN #Order o ON c.CustomerID =o.CustomerID

--Left Join, CustomerID 2 is returned
SELECT c.*,o.*
FROM #Customer c
LEFT JOIN #Order o ON c.CustomerID =o.CustomerID

--Right Join, CustomerID 2 is returned
SELECT c.*,o.*
FROM #Order o
RIGHT JOIN #Customer c ON c.CustomerID =o.CustomerID

--Return all the people without an order
SELECT c.*,o.*
FROM #Customer c
LEFT JOIN #Order o ON c.CustomerID =o.CustomerID
WHERE o.CustomerID IS NULL

--Left Join, CustomerID 2 is not returned because the condition is in the WHERE clause
--The WHERE clause is applied to the #Order table instead of the #Customer table
SELECT c.*,o.*
FROM #Customer c
LEFT JOIN #Order o ON c.CustomerID =o.CustomerID
WHERE o.CustomerID < 3

--Left Join, CustomerID 2 is returned (as well as 3) because the condition is in the AND clause
SELECT c.*,o.*
FROM #Customer c
LEFT JOIN #Order o ON c.CustomerID =o.CustomerID
AND o.CustomerID < color="#009900">


--The 'correct' way, applying the WHERE condition to the #Customer table
SELECT c.*,o.*
FROM #Customer c
LEFT JOIN #Order o ON c.CustomerID =o.CustomerID
WHERE c.CustomerID < 3


DROP TABLE #Order,#Customer