An inner join drops rows without a partner. Sometimes you need them: "every customer, with their orders if they have any".
sql
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;LEFT JOIN keeps every row of the left table, the one before LEFT JOIN. A left row with no partner appears once, with NULL in every column of the right table.
customers c
| id | name |
|---|---|
| 8 | Omar Haddad |
| 9 | Yuki Tanaka |
| 10 | Hannah Weber |
orders o
| id | customer_id |
|---|---|
| 107 | 8 |
| 114 | 10 |
| 115 | 8 |
customers c LEFT JOIN orders o ON o.customer_id = c.id
| c.name | o.id |
|---|---|
| Omar Haddad | 107 |
| Omar Haddad | 115 |
| Yuki Tanaka | NULL |
| Hannah Weber | 114 |
LEFT OUTER JOIN is the same. A join that keeps rows without a partner is an outer join.
The table order matters. orders o LEFT JOIN customers c keeps every order. Every order has a customer, so it returns the same rows as the inner join.
Find the rows without a partner
The NULLs mark the left rows that have no partner. Keep only those rows:
sql
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Test a column that is never NULL in a real match, such as the right table's primary key.