LEFT JOIN

INNER JOIN keeps only rows with a match in both tables. Lucas Martin has never ordered, so an inner join of customers and orders leaves him out completely. A LEFT JOIN keeps every row from the left (first) table, matched or not:

SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Where no order matches, the order columns are NULL.

customers          orders              LEFT JOIN result
Asha        ───►   101, 103, 111       Asha 101 · Asha 103 · Asha 111
Liam        ───►   102, 109            Liam 102 · Liam 109
...
Lucas       ───►   (none)              Lucas NULL   ◄ kept anyway

Finding rows with no match

Filter for those NULLs to find customers who have never ordered:

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

This pattern answers a whole family of questions: products that never sold, students who haven't submitted, users who never logged in.

Counting with LEFT JOIN

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;

Count a column from the right-hand table, COUNT(o.order_id), not COUNT(*). Lucas's row exists (filled with NULLs), so COUNT(*) would count it and claim he has one order.

ON or WHERE?

In a LEFT JOIN, where you put a condition changes the answer.

In ON, the condition decides which orders match, and every customer still appears:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
ORDER BY c.customer_id;

In WHERE, it filters after the join and throws away the NULL rows, so the query quietly becomes an inner join:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
ORDER BY c.customer_id;

Ten rows versus seven: Fatima, Ethan and Lucas have no delivered orders, and only the first query still lists them.

RIGHT and FULL OUTER JOIN

A RIGHT JOIN B is the same as B LEFT JOIN A, so most people write LEFT JOINs and put the table they want to keep first. SQLite has supported RIGHT and FULL OUTER JOIN since version 3.39 (2022); MySQL has RIGHT JOIN but no FULL OUTER JOIN.