Left Join
LEFT JOIN performs a join starting with the first (before join operation) table and then any matching second (after join operation) table records.
The result is NULL in the right side when there is no match.
SQL LEFT JOIN Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example:
Lets use the “Order” and the “Customers” tables to find all of the customer names related to the Orders.
| OrderID | ProductID | EmployeeID | OrderDate | CustomerID |
|---|---|---|---|---|
| 10 | 2 | 1 | 9/9/2009 | 2 |
| 11 | 3 | 2 | 1/9/2009 | 3 |
| 12 | 4 | 1 | 3/9/2009 | 4 |
| 13 | 1 | 3 | 9/9/2010 | 5 |
| 14 | 3 | 1 | 9/30/2009 | 3 |
| 15 | 5 | 2 | 6/12/2009 | 1 |
| 16 | 3 | 4 | 4/9/2009 | 6 |
| CustomerID | FirstName | LastName | Address | City | State | zip |
|---|---|---|---|---|---|---|
| 1 | Rob | Johnson | 1 York Rd | New York | NY | 10013 |
| 2 | Jessica | Simmons | 2 High St | Maimi | FL | 33101 |
| 3 | Tracy | Baker | 3 River Dr | Chicago | IL | 60633 |
| 4 | Tod | Williams | 4 Cabin Ln | Denver | CO | 80210 |
| 5 | Tammy | Wilson | 5 Peach St | Seattle | WA | 98121 |
| 6 | Mike | Jones | 6 Rice Rd | Los Angeles | CA | 90009 |
SELECT
O.OrderID,
O.CustomerID,
C.FirstName,
C.LastName
FROM Orders as O
LEFT JOIN Customers as C
ON O.CustomerID=C.CustomerID
Results:
| OrderID | CustomerID | FirstName | LastName |
|---|---|---|---|
| 10 | 2 | Jessica | Simmons |
| 11 | 3 | Tracy | Baker |
| 12 | 4 | Tod | Williams |
| 13 | 5 | Tammy | Wilson |
| 14 | 3 | Tracy | Baker |
| 15 | 1 | Rob | Johnson |
| 16 | 6 | Mike | Jones |
SQL Tutorial