FULL OUTER JOIN and CROSS JOIN

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.department_id = d.id;

FULL OUTER JOIN returns ALL rows from both tables. Unmatched rows from either side get NULLs. It is the combination of LEFT JOIN + RIGHT JOIN results.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

UNION combines LEFT JOIN and RIGHT JOIN results, removing duplicates. Use UNION ALL if you want duplicates kept (faster). This is a very common MySQL interview question.

-- Returns every combination of rows from both tables:
SELECT a.size, b.color
FROM sizes a
CROSS JOIN colors b;

-- If sizes has 3 rows and colors has 4 rows,
-- result has 3 x 4 = 12 rows

CROSS JOIN (Cartesian product) has no ON condition. It multiplies rows. Use cases: generating date ranges, creating test data, or combination matrices.

-- UNION: removes duplicates (slower)
SELECT name FROM table_a
UNION
SELECT name FROM table_b;

-- UNION ALL: keeps duplicates (faster)
SELECT name FROM table_a
UNION ALL
SELECT name FROM table_b;

UNION ALL is faster because it skips the deduplication step. Use UNION ALL by default and only use UNION if you specifically need to remove duplicates.

-- Using EXCEPT (PostgreSQL, SQL Server):
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;

-- Using NOT IN (works everywhere):
SELECT * FROM table_a
WHERE id NOT IN (SELECT id FROM table_b);

-- Using LEFT JOIN anti-join (most efficient):
SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;

Three ways to find set differences. The LEFT JOIN anti-join method is usually the most performant. NOT IN has a NULL trap — if table_b has any NULL id, NOT IN returns no rows.