LEFT JOIN — Keep the Lonely Rows

~12 min · joins, left-join, outer-join

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Outer joins keep unmatched rows

A LEFT JOIN returns every row from the left table, with NULLs filled in for columns from the right table when there is no match. Use it when you want to preserve all rows from one side regardless of whether the join finds a partner.

SQLite supports LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. The latter two were added in 3.39 (2022) — older code (and old answers on Stack Overflow) often emulates them with two LEFT JOINs UNIONed together.

Tip: LEFT JOIN ... WHERE rhs.col IS NULL is the canonical 'rows in A with no match in B' pattern. It reads more naturally than the equivalent NOT EXISTS form to many people.

Code

Authors with their post counts (including 0 for inactive authors)·sql

SELECT a.id, a.name, count(p.id) AS post_count
FROM   authors a
LEFT   JOIN posts p ON p.author_id = a.id
GROUP  BY a.id
ORDER  BY post_count DESC;

Find rows in A with no match in B·sql

-- Authors who haven't written anything
SELECT a.id, a.name
FROM   authors a
LEFT   JOIN posts p ON p.author_id = a.id
WHERE  p.id IS NULL;

External links

Exercise

On the authors+posts setup, write the same logical question two ways: (1) LEFT JOIN with WHERE IS NULL, (2) NOT EXISTS subquery. Compare the EXPLAIN QUERY PLAN output. Then write one more query that returns each author with their most recent post title (or NULL if none) — that's a LEFT JOIN with a correlated subquery or a window function.

LEFT JOIN — Keep the Lonely Rows

Outer joins keep unmatched rows

Code

External links

Exercise

Progress

Comments 0