LEFT JOIN — Include All Records from the Left Table

A LEFT JOIN returns all rows from the left table, and the matching rows from the right table.

If there is no match, the result will still include the left table's data, and show NULL for the right table's columns.

Syntax

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example Scenario

We’ll use the same two tables:

customers

+-------------+--------+-------------+-----------+
| customer_id | name   | city        | country   |
+-------------+--------+-------------+-----------+
| 1           | Alice  | New York    | USA       |
| 2           | Bob    | Los Angeles | USA       |
| 3           | Emma   | London      | UK        |
| 4           | Paul   | Berlin      | Germany   |
| 5           | Olivia | Madrid      | Spain     |
+-------------+--------+-------------+-----------+

orders

+----------+-------------+--------------+--------------+
| order_id | customer_id | product_name | order_status |
+----------+-------------+--------------+--------------+
| 101      | 1           | Laptop       | Delivered    |
| 102      | 1           | Phone        | Shipped      |
| 103      | 2           | Charger      | Cancelled    |
| 104      | 3           | Watch        | Delivered    |
| 105      | 4           | Headphones   | Shipped      |
| 106      | 4           | Speakers     | Pending      |
+----------+-------------+--------------+--------------+

Example Query

SELECT customers.name, orders.product_name, orders.order_status
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This returns all customers, even if they haven’t placed any orders. If there's no order, the product and status will be NULL.

Output

+--------+--------------+--------------+
| name   | product_name | order_status |
+--------+--------------+--------------+
| Alice  | Laptop       | Delivered    |
| Alice  | Phone        | Shipped      |
| Bob    | Charger      | Cancelled    |
| Emma   | Watch        | Delivered    |
| Paul   | Headphones   | Shipped      |
| Paul   | Speakers     | Pending      |
| Olivia | NULL         | NULL         |
+--------+--------------+--------------+

Now you can clearly see the difference — Olivia appears in the result even though she hasn’t placed any orders.

When to Use LEFT JOIN?

  • When you want all data from the left table.
  • Useful for finding unmatched records, like customers without any orders.

Next, let’s look at RIGHT JOIN, which works the opposite way!