SQL Joins Deep Dive Cheat Sheet

-- 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: 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;

-- 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;

-- Top 3 most recent orders per customer, without a window functionSELECT c.name, recent.order_id, recent.order_dateFROM customers cCROSS JOIN LATERAL (  SELECT o.id AS order_id, o.order_date  FROM orders o  WHERE o.customer_id = c.id  ORDER BY o.order_date DESC  LIMIT 3) recent;-- LEFT JOIN LATERAL keeps customers with zero orders (LATERAL result can be empty)SELECT c.name, recent.order_idFROM customers cLEFT JOIN LATERAL (  SELECT o.id AS order_id FROM orders o  WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 1) recent ON true;-- SQL Server / T-SQL equivalent keyword: CROSS APPLY / OUTER APPLY

EXPLAIN ANALYZESELECT o.id, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.status = 'shipped';-- Nested Loop: cheap when the outer side is small and an index exists--   on the inner side's join column; scales as O(outer_rows * index lookup)-- Hash Join: builds a hash table on the smaller input, probes with the--   larger; good for equi-joins with no useful index, needs work_mem-- Merge Join: both inputs pre-sorted (or sorted for the join) then--   merged in one pass; efficient when data is already ordered--   (e.g., joining on a clustered/primary key range scan)-- Force a plan shape to compare costs (Postgres, for diagnosis only):SET enable_nestloop = off;EXPLAIN ANALYZE SELECT ...;RESET enable_nestloop;

-- Emulated FULL OUTER JOIN: union of LEFT JOIN and the unmatched RIGHT rowsSELECT c.id AS customer_id, c.name, o.id AS order_idFROM customers cLEFT JOIN orders o ON o.customer_id = c.idUNIONSELECT c.id AS customer_id, c.name, o.id AS order_idFROM customers cRIGHT JOIN orders o ON o.customer_id = c.id;-- UNION (not UNION ALL) dedupes the rows that both queries return-- (the matched rows), leaving each unmatched row from either side exactly once.

-- Composite key join (both columns must match)SELECT *FROM shipments sJOIN inventory i  ON s.warehouse_id = i.warehouse_id AND s.sku = i.sku;-- Non-equi (range) join: attach the pricing tier active on the order dateSELECT o.id, o.order_date, p.tier_nameFROM orders oJOIN price_tiers p  ON o.order_date >= p.valid_from AND (o.order_date < p.valid_to OR p.valid_to IS NULL);-- Non-equi joins can't use a plain b-tree equality index efficiently;-- ensure valid_from/valid_to are indexed and the range is selective,-- or the planner falls back to a nested loop scanning every tier.