CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 2 min read

Full join and cross join

Keeping unmatched rows from both sides, and pairing everything with everything.

Full join and cross join

Think of merging two separate class lists from two different years into one combined roster, keeping every name from either year even where a student appears in only one of the two years.

In plain words: a full join keeps every row from both tables, filling NULL on whichever side has no match. A cross join pairs every row of one table with every row of another, with no join condition at all, which is almost never what is actually wanted unless it is deliberately the Cartesian product from the relational algebra page.

A worked example. A full join between two years' enrollment lists keeps students present in either year. A cross join between customers and books, with no condition, produces a pairing for every customer with every book.

The key lines.

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

SELECT c.full_name, b.title
FROM customers c
CROSS JOIN books b;

Full join, cross join

A full join keeps every row from both tables, filling NULL wherever one side has no match. A cross join pairs every row of one table with every row of another, with no matching condition.

Example: customers CROSS JOIN books, with 400 customers and 2,000 books, produces 800,000 rows, matching the Cartesian product from the relational algebra page exactly.

Remember this

An accidental cross join, from a missing or mistyped ON condition, is one of the most common real SQL mistakes. A result with far more rows than expected is the first sign to check the join condition.

Quick notes

  • Inner join: only matching rows on both sides.
  • Left join: every left row, NULL for unmatched right columns.
  • Right join: every right row, NULL for unmatched left columns.
  • Full join: every row from both sides, NULL wherever the other side has no match.
  • Cross join: every row paired with every row, with no condition; almost always a mistake if unintentional.