SQL-01-010-P · Primer — LEFT, RIGHT, and FULL OUTER JOINs

The idea in one sentence

An INNER JOIN quietly deletes any row that has no match on the other side; an outer join promises to keep those unmatched rows anyway, and pads the columns it couldn't fill with NULL — so a LEFT JOIN keeps every row from the left table, a RIGHT JOIN keeps every row from the right, and a FULL OUTER JOIN keeps every row from both.

Why it exists

You already know how to join two tables and pull matching rows together. But an INNER JOIN has a sharp, easy-to-miss edge: if a row on one side has no partner on the other, it simply disappears from the result. That's exactly what you want when you're asking "show me orders and their customers." It is exactly not what you want when you're asking "which customers have never placed an order?" — because those customers, having no matching order, are the very rows an inner join throws away.

This is the recurring problem outer joins solve: you often care about the rows that don't match. Employees not yet assigned to a department. Products that have never sold. Departments with nobody in them. Users who signed up but never logged in. Every one of these is a question about absence, and an inner join can't answer a question about absence, because it only shows you presence.

An outer join fixes this by declaring one (or both) tables preserved: every row from a preserved table survives into the result, matched or not. When there's no match, the database can't leave the other table's columns blank — a row must be rectangular — so it fills them with NULL, SQL's marker for "nothing here." Those NULLs aren't a bug; they're the whole point. They're the fingerprint of an unmatched row, and learning to read them is learning to answer "which ones are missing?"

How it works

A join walks the left table row by row and, for each row, looks for matches in the right table using the ON condition. An INNER JOIN emits a row only when it finds a match. LEFT JOIN (short for LEFT OUTER JOIN) does everything inner does, plus: if a left row found no match, it emits that left row once anyway, with every right-table column set to NULL. RIGHT JOIN is the mirror image — it preserves the right table's unmatched rows instead. FULL OUTER JOIN preserves both sides at once: unmatched left rows appear with NULL on the right, unmatched right rows appear with NULL on the left, and matched rows appear normally. In the example below, employee Cleo has no department (dept_id is NULL, so it matches nothing) and the Marketing department has no employees — watch how each join keeps or drops those two:

CREATE TABLE employees   (id INTEGER, name TEXT, dept_id INTEGER);
CREATE TABLE departments (id INTEGER, dept_name TEXT);

INSERT INTO employees VALUES
  (1,'Ana',  10), (2,'Ben', 20), (3,'Cleo', NULL), (4,'Dara', 10);
INSERT INTO departments VALUES
  (10,'Sales'), (20,'Engineering'), (30,'Marketing');   -- Marketing has nobody

-- LEFT JOIN: keep EVERY employee, matched or not
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
ORDER BY e.id;

-- name  dept_name
-- ----  -----------
-- Ana   Sales
-- Ben   Engineering
-- Cleo  NULL          <- kept! no department, so dept_name is NULL
-- Dara  Sales

-- FULL OUTER JOIN: keep EVERY employee AND EVERY department
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id
ORDER BY e.id;

-- name  dept_name
-- ----  -----------
-- NULL  Marketing     <- kept! department with no employees, so name is NULL
-- Ana   Sales
-- Ben   Engineering
-- Cleo  NULL          <- kept! employee with no department
-- Dara  Sales

The LEFT JOIN keeps Cleo (unmatched employee → NULL department) but silently drops Marketing, because departments are not the preserved side. The FULL OUTER JOIN keeps both orphans. To answer "which employees have no department?" you just add WHERE d.id IS NULL to the left join — that's the classic anti-join, and it works precisely because the unmatched rows are the ones wearing NULL.

The one gotcha

Filtering the preserved table's non-key columns in the WHERE clause silently turns your outer join back into an inner join. Say you write a LEFT JOIN to keep all employees, then add WHERE d.dept_name = 'Sales'. It feels harmless, but it's not: the NULL-padded rows (Cleo, and anyone else without a department) have d.dept_name = NULL, and NULL = 'Sales' is not true — so WHERE discards them. You asked to keep every employee, then quietly deleted the very unmatched rows the outer join went to the trouble of preserving. The result is identical to an inner join, and the "keep everyone" promise is gone. The rule: conditions that belong to the right (non-preserved) table go in the ON clause, not WHERE. ... LEFT JOIN departments d ON e.dept_id = d.id AND d.dept_name = 'Sales' filters while matching, so unmatched employees still survive with NULLs intact. Reserve WHERE d.something IS NULL for when you want only the unmatched rows (the anti-join). Everything else about the non-preserved table belongs in ON. And remember: NULL never equals anything, not even another NULL, so always test for it with IS NULL / IS NOT NULL, never = NULL.

See it — how each join keeps or drops unmatched rows

flowchart TB
    A["For each pair of rows,<br/>test the ON condition"] --> B{"Did the rows<br/>match?"}
    B -- "yes" --> C["Emit the joined row<br/>(all joins agree here)"]
    B -- "no, left row<br/>unmatched" --> D{"Is the LEFT table<br/>preserved?"}
    B -- "no, right row<br/>unmatched" --> E{"Is the RIGHT table<br/>preserved?"}
    D -- "LEFT / FULL" --> F["Keep left row,<br/>right columns = NULL"]
    D -- "INNER / RIGHT" --> G["Drop the row"]
    E -- "RIGHT / FULL" --> H["Keep right row,<br/>left columns = NULL"]
    E -- "INNER / LEFT" --> I["Drop the row"]

The one-line takeaway

INNER keeps only matches; LEFT also keeps the left table's orphans, RIGHT also keeps the right table's orphans, and FULL OUTER keeps both sides' orphans — with NULL filling every column the missing side would have supplied.

Where you'll meet it

You'll reach for an outer join the moment a question is really about absence: customers who've never ordered, products with zero sales, students not enrolled in any class, sensors that stopped reporting. The pattern is a LEFT JOIN followed by WHERE <right>.id IS NULL — the anti-join — and it's one of the most reused shapes in all of SQL. You'll also lean on LEFT JOIN constantly in reports where you must show every item on the left (every product, every region, every day of the month) even when there's no activity to pair it with, so a dashboard shows a clean 0 or a blank instead of dropping the row entirely. In interviews this is a favorite, and it's usually a trap in two flavors: they'll ask "find users with no orders" (and watch whether you use a LEFT JOIN + IS NULL or fumble toward an inner join that can never return them), or they'll show you a LEFT JOIN with a WHERE filter on the right table and ask why it "isn't keeping all the rows" — the accidental-inner-join gotcha above. Know both, and know that FULL OUTER JOIN is the tool for reconciling two lists that each contain items the other is missing (old system vs. new system, expected vs. actual), where you need to see mismatches on both sides at once.

Named concepts: LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, unmatched rows as NULL