FULL OUTER JOIN and CROSS JOIN

Step 1 Understand it

Why you need it. Comparing two lists — "which employees have no department, and which departments have no employees?" — needs the unmatched rows from both sides at once. Building a report grid — every customer × every month, zeros included — needs every combination. These two joins do those jobs.

Watch it happen

Picture it

flowchart TD
  subgraph F["FULL OUTER JOIN"]
    F1["matched pairs"]
    F2["left rows with no match"]
    F3["right rows with no match"]
  end
  subgraph X["CROSS JOIN: 2 customers × 3 months"]
    X1["Tom · Jan"] --- X2["Tom · Feb"] --- X3["Tom · Mar"]
    X4["Lena · Jan"] --- X5["Lena · Feb"] --- X6["Lena · Mar"]
  end

The idea

FULL OUTER JOIN = a LEFT JOIN and a RIGHT JOIN together: matches as usual, plus the unmatched left rows (right columns NULL) and the unmatched right rows (left columns NULL). It is mainly for reconciling two sources. CROSS JOIN has no ON at all: 6 customers × 3 months = 18 rows. On its own that is rarely what you want, but it is the standard way to build a complete grid, which you then LEFT JOIN the real data onto so gaps show as zero.

The shape of it

SELECT ...
FROM a FULL OUTER JOIN b ON b.key = a.key;

SELECT ...
FROM a CROSS JOIN b;

Words in lower case are the parts you fill in.

How it works, step by step

1. FULL OUTER JOIN employees and departments: the 7 matched pairs, plus Gus (no department), plus HR (no employees).
2. Gus's row has NULL department columns; HR's row has NULL employee columns.
3. CROSS JOIN customers and months: each customer row is paired with each month row, with no condition.
4. LEFT JOIN the orders onto that grid on customer and month: cells with no orders get NULL.
5. COALESCE(SUM(amount), 0) turns those NULLs into zeros, so the report has no gaps.

Analogy

FULL OUTER JOIN is comparing two class registers after a merger: you list everyone who is on both, plus the names that are only on one register. CROSS JOIN is a timetable grid: every class against every period, whether or not anything is scheduled there.

Where the analogy breaks

A timetable grid is small. A cross join of two big tables is enormous — a million rows × a million rows is a trillion — so it is only safe when at least one side is tiny.

Worked examples

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON d.dept_id = e.dept_id
ORDER BY d.dept_id, e.name;

SELECT p.product_name, d.dept_name
FROM products p
CROSS JOIN departments d
WHERE p.category = 'Stationery'
ORDER BY p.product_name, d.dept_id;

SELECT c.name, m.month, COALESCE(SUM(o.amount), 0) AS spent
FROM customers c
CROSS JOIN (SELECT DISTINCT strftime('%Y-%m', order_date) AS month FROM orders) AS m
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND strftime('%Y-%m', o.order_date) = m.month
WHERE c.customer_id IN (2, 3)
GROUP BY c.customer_id, c.name, m.month
ORDER BY c.customer_id, m.month;

Must-know points

• FULL OUTER JOIN = matches + unmatched left rows + unmatched right rows.
• CROSS JOIN has no ON and returns rows(a) × rows(b).
• Grid pattern: CROSS JOIN the dimensions, LEFT JOIN the facts, COALESCE to 0.
• A join with a missing or always-true ON condition is an accidental cross join.

Common mistakes

• Running a CROSS JOIN between two large tables.
• Forgetting an ON condition and getting a cross join by accident — the row count explodes.
• Using WHERE on one side after a FULL OUTER JOIN, which removes the other side's unmatched rows.

In other databases

• MySQL has no FULL OUTER JOIN: write a LEFT JOIN, then UNION it with the unmatched rows of a RIGHT JOIN. SQLite added FULL OUTER JOIN in 3.39.
• PostgreSQL can build date grids directly with generate_series('2024-01-01', '2024-03-01', interval '1 month').

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL FULL OUTER JOIN

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL FULL OUTER JOIN

  Mode, free

• Practice

  SQLBolt — Lesson 7: OUTER JOINs

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain when you would use FULL OUTER JOIN instead of LEFT JOIN.

   Show answer

   Use LEFT JOIN when one table is the main list and you only care about its unmatched rows: "every department, even empty ones".

   Use FULL OUTER JOIN when unmatched rows on both sides matter: "everything in either list, and show me what doesn't line up". With employees and departments it shows Gus (no department) and HR (no employees) in the same result. In real work it is for comparing two sources, such as payments against invoices, or yesterday's file against today's: missing on the left, missing on the right, and matched.

   (MySQL has no FULL OUTER JOIN; you write a LEFT JOIN and a RIGHT JOIN and UNION them.)

2. Writefrom a blank page, then run it

   Build a grid of every product category × every order status, with the number of items sold in each cell (0 where none).

   Show answer

   Make the grid first: every category CROSS JOIN every status. Then LEFT JOIN the real numbers onto it, worked out in a subquery (items sold per category and status), and COALESCE the gaps to 0.

   The full grid is 5 categories × 4 statuses = 20 cells. The WHERE below keeps two categories so the result fits on screen; delete it to see all 20. Every 'cancelled' cell is 0, because the only cancelled order, 106, has no items.

   sqlCopy

   SELECT cat.category, st.status, COALESCE(s.items, 0) AS items_sold
   FROM (SELECT DISTINCT category FROM products) AS cat
   CROSS JOIN (SELECT DISTINCT status FROM orders) AS st
   LEFT JOIN (
     SELECT p.category, o.status, SUM(oi.quantity) AS items
     FROM order_items oi
     JOIN products p ON p.product_id = oi.product_id
     JOIN orders o   ON o.order_id = oi.order_id
     GROUP BY p.category, o.status
   ) AS s ON s.category = cat.category AND s.status = st.status
   WHERE cat.category IN ('Electronics', 'Home')
   ORDER BY cat.category, st.status;

   Try it

   8 rows

   category	status	items_sold
   Electronics	cancelled	0
   Electronics	delivered	8
   Electronics	pending	0
   Electronics	shipped	3
   Home	cancelled	0
   Home	delivered	5
   Home	pending	1
   Home	shipped	0

3. Predictsay it before you run it

   How many rows does SELECT * FROM customers CROSS JOIN departments; return?

   Show answer

   24 rows: every one of the 6 customers paired with every one of the 4 departments, 6 × 4. A CROSS JOIN has no ON, so nothing is matched and nothing is dropped. (It also has 8 columns, so the check below just counts.)

   sqlCopy

   SELECT COUNT(*) AS pairs
   FROM customers
   CROSS JOIN departments;

   Try it

   1 row

   pairs
   24

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   In the FULL OUTER JOIN example, add WHERE e.salary > 40000. Which rows disappear, and why does HR go too?

   Show answer

   Gus disappears, because he earns 38,000. HR disappears too, even though the filter is about salaries.

   HR's row has no employee, so e.salary is NULL there. NULL > 40000 is unknown, and WHERE drops anything that isn't true. The same happens to every row a FULL or LEFT JOIN filled with NULLs: a WHERE on the other table's columns removes them, and the outer join quietly becomes an inner join.

   To keep HR, filter the employees before the join (in a subquery, or in the ON), or allow the empty side: WHERE e.salary > 40000 OR e.emp_id IS NULL.

   sqlCopy

   SELECT e.name, d.dept_name
   FROM employees e
   FULL OUTER JOIN departments d ON d.dept_id = e.dept_id
   WHERE e.salary > 40000
   ORDER BY d.dept_id, e.name;

   Try it

   7 rows

   name	dept_name
   Asha	Engineering
   Ben	Engineering
   Chen	Engineering
   Hana	Engineering
   Dara	Sales
   Eli	Sales
   Fatima	Marketing

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

A FULL OUTER JOIN returns matched rows plus the unmatched rows from both sides, with NULLs filling the gaps — useful for reconciling two sources. A CROSS JOIN is the Cartesian product, every row paired with every row; I mostly use it to build a complete grid such as every customer by every month, then LEFT JOIN the facts onto it so missing combinations show as zero instead of disappearing.

Follow-up questions

Tick each check when you can do it without notes