The SQL LEFT JOIN (or LEFT OUTER JOIN) is used to combine rows from two tables based on a related column between them. It includes all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
Here’s the basic syntax for a LEFT JOIN:
SELECT column1, column2, ...
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;
Example
Let’s say we have two tables:
1. Customers (left table)
CustomerID | CustomerName
-----------|---------------
1 | John Doe
2 | Jane Smith
3 | Alice Johnson
2. Orders (right table)
OrderID | CustomerID | OrderDate
--------|------------|------------
101 | 1 | 2023-01-01
102 | 1 | 2023-01-02
103 | 2 | 2023-01-03
To retrieve all customers along with their orders (if they have any), you would use the following SQL query:
SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result Set
The result of this query will be:
CustomerID | CustomerName | OrderID | OrderDate
-----------|----------------|---------|------------
1 | John Doe | 101 | 2023-01-01
1 | John Doe | 102 | 2023-01-02
2 | Jane Smith | 103 | 2023-01-03
3 | Alice Johnson | NULL | NULL
In this result:
- John Doe has two orders, so he appears twice.
- Jane Smith has one order, so she is listed with her order.
- Alice Johnson has no orders, so her record includes NULL values for OrderID and OrderDate.
Key Points
- Use LEFT JOIN when you want to include all records from the left table regardless of whether there are matching records in the right table.
- If there is no match found, the fields from the right table will return NULL.