FULL & CROSS JOIN

Two more join types complete the set. FULL OUTER JOIN keeps unmatched rows from both sides — ideal for comparing two lists. CROSS JOIN pairs every row with every other row, which sounds useless but is surprisingly handy for generating combinations.

FULL OUTER JOIN

SELECT e.first_name, d.name AS department
FROM employees AS e
FULL OUTER JOIN departments AS d ON d.id = e.department_id
WHERE e.id IS NULL OR d.id IS NULL;

The result includes Olivia (no department) and Research (no employees) — the unmatched rows from each side. Matched pairs appear as usual; the WHERE here keeps only the unmatched ones.

MySQL doesn't support FULL JOIN; combine a LEFT JOIN and a RIGHT JOIN with UNION instead (lesson 28).

Comparing two lists

WITH cities_with_customers AS (SELECT DISTINCT city FROM customers WHERE city IS NOT NULL),
     office_cities AS (SELECT DISTINCT location AS city FROM departments WHERE location IS NOT NULL)
SELECT c.city AS customer_city, o.city AS office_city
FROM cities_with_customers AS c
FULL JOIN office_cities AS o ON o.city = c.city
ORDER BY COALESCE(c.city, o.city);

Cities with both customers and offices appear on one line; others have a NULL on one side. (WITH defines temporary named results — lesson 31.)

CROSS JOIN

SELECT d.name AS department, s.status
FROM departments AS d
CROSS JOIN (SELECT 'open' AS status UNION ALL SELECT 'closed') AS s
WHERE d.id <= 2;

A cross join returns every combination: 2 departments × 2 statuses = 4 rows. With no condition, the row count multiplies — two tables of 1,000 rows each produce a million rows, so use it deliberately.

Generating a complete grid

SELECT cat.category, m.month,
       COUNT(oi.order_id) AS lines_sold
FROM (SELECT DISTINCT category FROM products) AS cat
CROSS JOIN (SELECT DISTINCT strftime('%Y-%m', order_date) AS month FROM orders WHERE order_date >= '2026-01-01') AS m
LEFT JOIN products AS p ON p.category = cat.category
LEFT JOIN order_items AS oi ON oi.product_id = p.id
  AND oi.order_id IN (SELECT id FROM orders WHERE strftime('%Y-%m', order_date) = m.month)
GROUP BY cat.category, m.month
ORDER BY cat.category, m.month;

Reports often need a row for every combination — including zeros. Cross joining the categories with the months creates the full grid, and the left joins fill in the counts. Without it, combinations with no sales would simply be missing.

Accidental cross joins

SELECT COUNT(*) FROM customers, orders;

10 customers × 15 orders = 150 rows. Forgetting a join condition — easy with the comma syntax — produces this kind of explosion. If a result has far more rows than expected, look for a missing or incomplete join condition.

Join types at a glance

Try it yourself

Create a list of every product paired with every order status, for the Toys category.