LEFT JOIN: Keep All Rows from the Left Table

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

If a row in the left table has no match, it is still in the result. The columns from the right table are NULL.

The left table is the one after FROM. The right table is the one after JOIN.


Syntax

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

Example Query

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

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 |              | 
(7 rows)

This returns all customers, even the ones without orders. Olivia is in the result even though she has not placed an order. Her product and status are NULL, which psql shows as empty spaces.


Example: Find Customers Without Orders

A LEFT JOIN is a good way to find rows that have no match. Keep only the rows where the right side is NULL:

SELECT customers.name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

Output:

  name  
--------
 Olivia
(1 row)

When to Use LEFT JOIN?

  • When you want all rows from the left table, matched or not.
  • To find rows with no match, like customers without any orders.

Next, let's look at RIGHT JOIN, which works the other way around.

In the exercise, use LEFT JOIN to list every customer with their products.