FULL OUTER and CROSS JOIN

Keeping everything from both sides, and deliberate Cartesian products.

What you'll be able to do

Use FULL OUTER JOIN to reconcile two sets
Recognise an accidental cross join
Use CROSS JOIN deliberately

FULL OUTER JOIN

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

Every row from both tables appears, with nulls filled in where there is no match on either side.

MySQL does not support it; the workaround is a LEFT JOIN unioned with a RIGHT JOIN.

Reconciliation

The main practical use is comparing two data sets — a migration check, for instance:

SELECT COALESCE(a.id, b.id) AS id,
       a.total AS old_total,
       b.total AS new_total
FROM old_system a
FULL OUTER JOIN new_system b ON b.id = a.id
WHERE a.id IS NULL OR b.id IS NULL OR a.total <> b.total;

That returns everything that is missing on one side or disagrees between them.

CROSS JOIN

A cross join produces every combination — the Cartesian product. 100 rows joined to 50 gives 5,000.

SELECT s.size, c.colour
FROM sizes s CROSS JOIN colours c;

Deliberate uses are real: generating a date spine crossed with every product so that gaps show as zero rather than as missing rows.

The accidental cross join

Forgetting the ON clause, or writing tables comma-separated with no join condition in WHERE, produces a cross join by accident.

-- accidental Cartesian product
SELECT * FROM orders, customers;

The symptom is a query returning millions of rows and running for a very long time. If a result set is inexplicably huge, check for a missing join condition first.

Key points

FULL OUTER JOIN keeps unmatched rows from both sides
MySQL has no FULL OUTER JOIN — emulate with LEFT UNION RIGHT
A missing ON clause produces an accidental Cartesian product

Check yourself