SQL Joins Deep Dive Cheat Sheet

Breaks down INNER, LEFT/RIGHT/FULL OUTER, CROSS, and SELF joins with execution semantics and query examples for combining relational tables.

2 PagesIntermediateMar 8, 2026

Basic Join Syntax

INNER, LEFT, RIGHT, and FULL OUTER joins.

sql

-- INNER JOIN: only matching rows from both tablesSELECT o.id, c.nameFROM orders oINNER JOIN customers c ON o.customer_id = c.id;-- LEFT (OUTER) JOIN: all rows from left, NULLs if no match on rightSELECT c.name, o.idFROM customers cLEFT JOIN orders o ON o.customer_id = c.id;-- RIGHT (OUTER) JOIN: all rows from right, NULLs if no match on leftSELECT c.name, o.idFROM customers cRIGHT JOIN orders o ON o.customer_id = c.id;-- FULL OUTER JOIN: all rows from both, NULLs where no match (not in MySQL)SELECT c.name, o.idFROM customers cFULL OUTER JOIN orders o ON o.customer_id = c.id;

Self Join & Cross Join

Joining a table to itself and producing a Cartesian product.

sql

-- SELF JOIN: relate rows within the same table (e.g., employee -> manager)SELECT e.name AS employee, m.name AS managerFROM employees eJOIN employees m ON e.manager_id = m.id;-- CROSS JOIN: Cartesian product, every row of A with every row of BSELECT s.size, c.colorFROM sizes sCROSS JOIN colors c;

Join Types at a Glance

Quick reference for what each join returns.

INNER JOIN- Returns only rows where the join condition matches in both tables
LEFT JOIN- Returns all left-table rows, with NULLs for unmatched right-table columns
RIGHT JOIN- Returns all right-table rows, with NULLs for unmatched left-table columns; equivalent to swapping tables and using LEFT JOIN
FULL OUTER JOIN- Returns all rows from both tables, NULLs where no match exists; MySQL lacks native support, emulate with UNION of LEFT and RIGHT joins
CROSS JOIN- Produces the Cartesian product of both tables (rows_A * rows_B); use deliberately, it has no ON clause
SELF JOIN- A table joined to itself via aliases, used for hierarchical or comparative relationships
NATURAL JOIN- Joins automatically on columns with identical names in both tables; avoid in production, it's fragile to schema changes

Semi-Joins & Anti-Joins

Filtering on existence of related rows without duplicating output.

sql

-- Semi-join: customers who have at least one order (EXISTS is typically faster than IN)SELECT c.*FROM customers cWHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);-- Anti-join: customers with NO ordersSELECT c.*FROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);-- Anti-join via LEFT JOIN / IS NULL patternSELECT c.*FROM customers cLEFT JOIN orders o ON o.customer_id = c.idWHERE o.id IS NULL;

Pro Tip

Prefer NOT EXISTS over NOT IN for anti-joins when the subquery column can be NULL — NOT IN returns an empty result set entirely if any row in the subquery is NULL, a common silent bug.

Was this cheat sheet helpful?

Database Replication Cheat Sheet

SQL Window Functions Cheat Sheet