LEFT (OUTER) JOIN

A LEFT (OUTER) JOIN in SQL is a type of join that returns all the rows from the left table, and the matched rows from the right table. If there are no matches in the right table, the result is NULL on the right side. Here is the Venn diagram representation of the LEFT (OUTER) JOIN:

The syntax for a LEFT OUTER JOIN is as follows:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Let’s consider the same two tables, Orders and Customers:

Orders

OrderID  | CustomerID  | Product    | Quantity
-------- | ----------- | ---------- | --------
1        | 3           | Book       | 2
2        | 1           | Pen        | 3
3        | 2           | Notebook   | 1
4        | 5           | Pencil     | 4
5        | 1           | Eraser     | 1

Customers

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve
4          | Michael

Example Test Question

Prepare a list of all customers and their orders if they have made any. If a customer hasn’t made an order, the order fields should be displayed as NULL.

Answer

To solve this, we need to do a LEFT JOIN on the Customers and Orders tables. We would join them based on the CustomerID which is common in both tables.

Here is the SQL query to achieve this:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This would return the following result:

CustomerName  | OrderID  | Product  | Quantity
------------- | -------- | -------- | --------
John          | 2        | Pen      | 3
John          | 5        | Eraser   | 1
Maria         | 3        | Notebook | 1
Steve         | 1        | Book     | 2
Michael       | NULL     | NULL     | NULL

In the result, you can see that all customers are included, regardless of whether they’ve made an order or not. Where there is no corresponding order for a customer (in this case, ‘Michael’), the order fields are displayed as NULL.

This is how a LEFT OUTER JOIN works in SQL, providing a result set that includes all records from the left table and the matching records from the right table. If there is no match, the result is NULL on the right side.

To get all customers who did not place any orders we use the following query:

SELECT * FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
WHERE Orders.CustomerID IS NULL;

The SQL query above only returns the customers who have not placed any orders, in this case only Michael. Here is the resulting table:

CustomerID | CustomerName
---------- | ------------
4          | Michael

Which can be demonstrated as a Venn diagram as shown below: