Skip to content
Tutorials Center
My learning

Topics

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.

SQL
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

SQL Every employee, with or without a project ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Finding rows with no match ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 be NULL when matched (the primary key).
  • A WHERE on the right table turns it into an inner join. WHERE o.status = 'shipped' removes the NULL rows. Put such conditions in ON to keep unmatched left rows.
  • Count carefully. COUNT(*) counts the NULL row 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 B is not the same as B 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 NULL rows.
  • Equivalent to a RIGHT JOIN with the tables swapped, so many teams only use LEFT.