SQL Reference ยท Joins and Subqueries
SQL LEFT JOIN
Reference for SQL LEFT JOIN (LEFT OUTER JOIN): keep every row from the left table, fill NULLs where nothing matches, find missing rows and avoid WHERE mistakes.
LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the left table. Where a row has matching rows in the right table, they are paired as in an inner join; where it has none, the row still appears, with NULL in every column that comes from the right table.
Use it when "no match" is information you want to keep: customers who never ordered, projects with nobody assigned, authors without books.
SELECT c.id, o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM left_table LEFT [OUTER] JOIN right_table ON condition;
| Part | Description |
|---|---|
LEFT [OUTER] JOIN |
OUTER is optional. The table written before JOIN is the one kept completely. |
ON condition |
Decides which right rows match. Conditions on the right table that you want to keep optional belong here. |
Returns: all rows of the left table at least once, each paired with every matching right row, or with one row of NULLs if there is no match.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite, PostgreSQL, MySQL, SQL Server | Standard LEFT [OUTER] JOIN. |
| Oracle | Standard syntax works; old code uses a.id = b.a_id(+) in WHERE. |
| Old SQL Server | The *= operator was removed long ago; use LEFT JOIN. |
Usage notes
- Find missing rows with
WHERE right.key IS NULL, the anti-join pattern. Test a column that cannot beNULLwhen matched (the primary key). - A
WHEREon the right table turns it into an inner join.WHERE o.status = 'shipped'removes theNULLrows. Put such conditions inONto keep unmatched left rows. - Count carefully.
COUNT(*)counts theNULLrow as 1;COUNT(o.id)counts only real matches. - Duplicates multiply when the right table has several matches; joining two "many" tables to one parent inflates aggregates.
- Table order matters:
A LEFT JOIN Bis not the same asB LEFT JOIN A. - Chained left joins keep working if you keep joining from the preserved side; an inner join after a left join can discard the
NULLrows. - Equivalent to a
RIGHT JOINwith the tables swapped, so many teams only useLEFT.
Related
INNER JOIN,RIGHT JOIN,FULL OUTER JOIN, anti-joins,IS NULL- Lessons: LEFT JOIN, Right and full joins, Joins