The LEFT JOIN keyword retrieves all records from the left table (table1) and any matching records from the right table (table2). If there’s no match, the result will show NULL for records from the right side.
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; |
Note: In certain databases, LEFT JOIN is referred to as LEFT OUTER JOIN.
In this tutorial, we’ll utilize the popular Northwind sample database.
Here’s a segment from the “Customers” table:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
And a selection from the “Orders” table:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10308 |
2 |
7 |
1996-09-18 |
3 |
10309 |
37 |
3 |
1996-09-19 |
1 |
10310 |
77 |
8 |
1996-09-20 |
2 |
This SQL statement retrieves all customers along with any associated orders they may have.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; |
Keep in mind: The LEFT JOIN keyword retrieves all records from the left table (Customers), irrespective of whether there are corresponding matches in the right table (Orders).