LEFT JOIN

A LEFT JOIN returns every row from the left (first) table, plus matching rows from the right table. Where there is no match, the right table's columns come back as NULL.

SQL

LEFT JOIN is useful when you want to keep all records from one table regardless of whether they have related data — for example, listing all customers including those who have never placed an order.

It is also called LEFT OUTER JOIN. A common trick is to add 'WHERE right_table.key IS NULL' to find rows in the left table that have no match at all.

Syntax

SELECT columns
FROM table_a
LEFT JOIN table_b ON table_a.key = table_b.key;

Examples

SQL

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Output:

name   total
-----  -----
Alice  50
Bob    NULL

Learn it in a lesson

See also

Back to the Dictionary