FULL JOIN

Objectives

By the end of this lesson, you should be able to:

• Keep every row from both tables with FULL JOIN
• Explain when FULL JOIN is the right choice over LEFT JOIN or INNER JOIN

💡 Why this matters: Some questions need both directions of “unmatched” at once, “every category, even empty ones, and every product, even ones with no category set.” Neither LEFT JOIN nor RIGHT JOIN alone covers both sides.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INTEGER REFERENCES categories(id)
);

INSERT INTO categories (name) VALUES ('Electronics'), ('Home'), ('Office'), ('Outdoors');
INSERT INTO products (name, category_id) VALUES
  ('Wireless Mouse', 1),
  ('Desk Lamp', 2),
  ('Notebook', 3),
  ('Mystery Item', NULL);

Outdoors has no products in it, and Mystery Item has no category set (category_id is NULL, allowed since this foreign key column isn’t NOT NULL). Both kinds of “unmatched” exist in this data.

FULL JOIN

SELECT c.name AS category, p.name AS product
FROM categories c FULL JOIN products p ON c.id = p.category_id
ORDER BY category NULLS LAST, product NULLS LAST;

   category   |     product
---------------+------------------
 Electronics  | Wireless Mouse
 Home         | Desk Lamp
 Office       | Notebook
 Outdoors     |
              | Mystery Item

FULL JOIN (also written FULL OUTER JOIN, OUTER is optional and usually omitted) keeps every row from both tables. Outdoors appears with a NULL product, no matching row in products. Mystery Item appears with a NULL category, no matching row in categories. Neither LEFT JOIN nor RIGHT JOIN alone would show both of these gaps in the same result, LEFT JOIN categories to products would show Outdoors but not Mystery Item, and the reverse would show Mystery Item but not Outdoors.

When to Reach for FULL JOIN

FULL JOIN comes up less often than INNER JOIN or LEFT JOIN in everyday queries, but it’s exactly right for reconciliation-style questions: “show me every category and every product, and make any gaps on either side visible,” useful for spotting empty categories and uncategorized products in the same report.

Try It

1. Write the FULL JOIN query above and confirm both Outdoors and Mystery Item appear with a NULL on the other side.
2. Add a WHERE p.id IS NULL clause to isolate only categories with no products.
3. Add a WHERE c.id IS NULL clause (instead) to isolate only products with no category.
4. Explain, in your own words, the difference between what LEFT JOIN, RIGHT JOIN, and FULL JOIN each guarantee about which rows survive.

Recap

• FULL JOIN (or FULL OUTER JOIN) keeps every row from both tables, filling in NULL wherever no match exists on the other side.
• It’s the only join type that surfaces unmatched rows from both directions in a single query.
• Combined with IS NULL on either side’s key column, it isolates exactly the unmatched rows from that side.

Next lesson: CROSS JOIN, every combination of rows from both tables, with no matching condition at all.