FULL OUTER & CROSS JOIN

Overview

FULL OUTER JOIN keeps every row from BOTH tables — matching rows are combined, and unmatched rows from either side get NULL-padded columns on the other side. It is the union of a LEFT JOIN and a RIGHT JOIN. CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right table, with no ON condition. If left has M rows and right has N rows, you get M × N result rows. Against the shared dataset, consider a small example — joining all customers to the first 3 products via CROSS JOIN: SELECT c.name, p.name AS product FROM customers c CROSS JOIN products p WHERE p.id <= 3 ORDER BY c.name, p.name; This returns 8 × 3 = 24 rows — every customer paired with Keyboard, Monitor, and Mouse. FULL OUTER JOIN example — suppose you want to see which customers have orders and which orders have customers (all of both): SELECT c.name, o.id AS order_id FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.id ORDER BY c.name, o.id; All 8 customers appear (Greg and Hana with order_id = NULL), and all 8 orders appear matched to their customers. In this dataset every order has a valid customer_id, so no order row is orphaned — but if one were, it would show with name = NULL. Mental model: FULL OUTER = 'keep everything from both sides, match what you can'. CROSS = 'pair everything with everything'.

FULL OUTER JOIN is the go-to tool for data reconciliation — comparing two sets to find what is in A but not B, what is in B but not A, and what overlaps. CROSS JOIN is used to generate combinations (e.g., all product–region pairs for a price matrix) or expand a calendar of dates against a list of entities.

Where used: data reconciliation between two systems, generating all product-size combinations for a catalog, calendar × entity grids for reporting

Why learn this

• FULL OUTER JOIN lets you find orphaned records on both sides of a relationship in a single query — essential for data quality audits
• CROSS JOIN is the foundation of generating combinatorial test data and dense reporting grids

Query walkthrough

SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

Focus: Watch both sides preserved: Greg and Hana appear with order_id = NULL (unmatched left rows). All orders also appear because every order has a valid customer — but if an order had an invalid customer_id, it would show with name = NULL (unmatched right row).

Common mistakes

• Using CROSS JOIN when you meant INNER JOIN: CROSS JOIN produces every combination without a matching condition. If you accidentally use CROSS JOIN instead of INNER JOIN between customers (8 rows) and orders (8 rows), you get 64 rows instead of 8. Always check whether you need a matching condition.
• Filtering FULL OUTER JOIN results in WHERE destroys outer rows: Adding WHERE o.status = 'paid' to a FULL OUTER JOIN removes any row where o.status is NULL — which includes the outer-joined customers with no orders. Move the filter into the ON clause to preserve unmatched rows.
• Expecting FULL OUTER JOIN to deduplicate: FULL OUTER JOIN does not eliminate duplicates. If a customer has 3 orders, that customer appears 3 times — once per matched order — plus unmatched rows from either side get their own NULL-padded row.

Glossary

Cartesian

A mathematical term describing a result where every single item in one list is paired with every single item in another list.

orphaned

A record or row of data that has lost its connection to related data in another table, like an order without a customer.

reconciliation

The process of comparing two sets of data to ensure they match and to identify any differences or missing pieces.

Recall questions

• How does FULL OUTER JOIN differ from LEFT JOIN?
• Does CROSS JOIN use an ON clause?
• If table A has 5 rows and table B has 4 rows, how many rows does CROSS JOIN produce?

Understanding checks

What rows does this return?

Greg, NULL Hana, NULL

The FULL OUTER JOIN keeps all customers and all orders. Greg and Hana have no orders, so their order_id is NULL. Every order in the dataset has a valid customer_id, so no order is orphaned (no row with c.name IS NULL). The WHERE keeps only unmatched rows from either side — here just Greg and Hana.

How many rows does this return, and what are they?

6 rows: Alice, Chair Alice, Desk Bob, Chair Bob, Desk Greg, Chair Greg, Desk

US customers are Alice, Bob, and Greg (3 rows). Furniture products are Desk and Chair (2 rows). CROSS JOIN pairs every US customer with every Furniture product = 3 × 2 = 6 rows.

Practice tasks

Switch from INNER to FULL OUTER

This query uses INNER JOIN, so customers without orders and any orphaned orders are hidden. Change it to a FULL OUTER JOIN so that both unmatched customers and unmatched orders appear. Keep the ordering.

Challenge

Every customer × every product category

Write a query that returns every distinct combination of customer name and product category using a CROSS JOIN and DISTINCT. Order by customer name, then category.

Continue learning

Previous: INNER JOIN

Previous: LEFT / RIGHT JOIN

Return to SQL Roadmap