⚡ CrackLab
beginner~4h

JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS

JOINs combine rows from two or more tables based on a related column. Understanding which JOIN to use and how it executes is fundamental to SQL mastery.

Relational databases deliberately split data across multiple tables to avoid duplication (normalization) — but that means answering almost any real question requires recombining that data, and JOIN is the only mechanism SQL gives you to do it.

If related data always lived in one giant table instead, you'd get massive duplication (the same customer's name repeated on every one of their orders) and an update-anomaly nightmare (change a customer's name in one row, forget the other 500). Splitting into separate tables and joining on demand is how relational databases avoid that tradeoff entirely.

INNER JOIN — keeps only rows with a match in both tables. LEFT JOIN — keeps every row from the left table, filling unmatched right-side columns with NULL. FULL OUTER JOIN — keeps every row from both sides, matched or not. SELF JOIN — a table joined to itself, for hierarchical/recursive relationships. CROSS JOIN — every row from one table paired with every row from the other (a full Cartesian product).

JOINs combine rows from two or more tables based on a related column. Understanding which JOIN to use and how it executes is fundamental to SQL mastery.

JOIN types

  • INNER JOIN — returns rows that have matching values in BOTH tables. Most common.

  • LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the LEFT table, matched rows from right. Non-matches: NULL on right side.

  • RIGHT JOIN (RIGHT OUTER JOIN) — opposite of LEFT JOIN. Returns all rows from RIGHT table.

  • FULL OUTER JOIN — returns all rows from BOTH tables. NULLs where no match.

  • SELF JOIN — a table joined to itself. Used for hierarchies, finding duplicates, etc.

  • CROSS JOIN — Cartesian product: every row from left × every row from right. n × m rows.

JOIN performance

  • Nested Loop Join — good for small tables or indexed joins.

  • Hash Join — builds hash table on smaller table; good for large tables without indexes.

  • Merge Join — requires both inputs sorted; good for pre-sorted or indexed data.

PostgreSQL's optimizer chooses the join strategy automatically based on statistics.

Hash Join (most common for large tables without sorted indexes):

  • Build phase: scan the smaller table (build side); create a hash table keyed on the join column.

  • Probe phase: scan the larger table (probe side); for each row, look up the hash table.

  • Return matching rows.

  • Memory: hash table must fit in work_mem; if not, spills to disk (Hash Batches > 1 in EXPLAIN).

Merge Join (for pre-sorted inputs)

  • Sort both inputs by the join key (or use existing index order).

  • Simultaneously scan both sorted inputs; merge matching rows.

  • Very efficient when indexes provide sort order.

Nested Loop Join (for small outer table with index on inner):

  • For each row in the outer table: scan inner table using index on join column.

  • Efficient for: small outer result set + indexed inner table.

  • Inefficient for: large outer table (n² complexity without indexes).

Step 1: Identify which tables you need data from.

Step 2: Identify the join condition (usually foreign key = primary key).

Step 3: Decide: do you need ALL rows from one side (LEFT/RIGHT) or only matches (INNER)?

Step 4: Write ON clause explicitly — never use implicit comma join syntax.

Step 5: Add WHERE clause to filter after joining.

Step 6: Check for duplicate rows if joining to a table with multiple matching rows.

  • Always use explicit JOIN syntax — never implicit comma joins: FROM a, b WHERE a.id = b.a_id.

  • Use table aliases — makes long queries readable; required for SELF JOIN.

  • Start with INNER JOIN — add outer joins only when you need unmatched rows.

  • Add indexes on foreign key columns — critical for join performance.

  • Be careful with LEFT JOIN + WHERE on right table — turns it into INNER JOIN.

  • Use COALESCE for NULL handling on outer join columns.

  • Verify join result row count — if joining to a many-side without aggregation, you get row multiplication.

  • For anti-join (NOT IN set), use LEFT JOIN ... WHERE right_table.id IS NULL or NOT EXISTS (both efficient).

  • JOIN order matters for readability; optimizer reorders for performance.

  • Avoid non-equi joins (JOIN ON a.value BETWEEN b.low AND b.high) — no index support; prefer range tables.

  • Forgetting the ON clause — CROSS JOIN by accident with massive result set.

  • LEFT JOIN with filter on right table in WHERE — effectively becomes INNER JOIN. Fix: move condition to ON clause: LEFT JOIN orders o ON e.emp_id = o.emp_id AND o.status = 'COMPLETED'.

  • Duplicate rows from one-to-many join without aggregation — joining employees (6) to orders (7 orders) returns 7 rows if employee appears multiple times.

  • Using comma join syntax (deprecated): FROM a, b WHERE a.id = b.a_id — hard to read, easy to accidentally create CROSS JOIN.

  • SELF JOIN without alias — SQL requires aliases for self-join to distinguish the two instances.

  • NOT IN with NULLs: NOT IN (SELECT dept_id FROM departments) returns NO rows if any dept_id is NULL. Use NOT EXISTS instead.

  • CROSS JOIN on large tables — n × m rows; 1000 × 1000 = 1,000,000 rows.

  • Add foreign key indexes: CREATE INDEX idx_emp_dept ON employees(dept_id) — drastically improves join performance.

  • Check work_mem for hash joins: SET work_mem = '256MB' before heavy joins to prevent disk spill.

  • Use EXPLAIN ANALYZE to see join method chosen and actual rows vs estimated rows.

  • For large result sets: JOIN then GROUP BY reduces rows early.

  • Hash join spilling to disk: increase work_mem or add index for merge join.

  • Filter early: apply WHERE conditions before join (optimizer usually does this, but be explicit).

  • EXISTS vs JOIN for existence check: EXISTS short-circuits on first match; JOIN returns all matches.

A CROSS JOIN (or an accidentally missing JOIN condition, which produces the same effect) on two large tables can generate an enormous result set that exhausts memory or disk — this is a real availability risk, not just a performance nuisance, if it happens on a public-facing query endpoint with attacker-influenced parameters.

Watch EXPLAIN ANALYZE for join plans that degrade to a nested-loop scan over a large table — that's the single most common cause of a join query that was fast in testing becoming slow in production as table sizes grow. Alert on queries whose execution time scales non-linearly with a joined table's row count.

  • Monitor slow queries with auto_explain: logs full plan for queries > threshold.

  • Index foreign keys immediately after creation — unindexed FK degrades DELETE on parent table.

  • For multi-table reports: consider materialized views or summary tables updated by triggers.

  • Use JOIN hints sparingly (MySQL: STRAIGHT_JOIN, PostgreSQL: enable_hashjoin=off) — optimizer usually knows best.

  • Test join performance with production-like data volumes — small test data often gives misleading plans.

  • Write a query using INNER JOIN to show employees with their department name. Exclude employees with no department.

  • Write a LEFT JOIN query to find all departments that have NO employees. (Anti-join pattern.)

  • Write a SELF JOIN to find pairs of employees in the same department where one earns more than 20% more than the other.

  • Using FULL OUTER JOIN, find: employees with no department AND departments with no employees in a single query.

  • INNER JOIN: only matching rows from both tables.

  • LEFT JOIN: all left rows + matching right (non-matches = NULL).

  • RIGHT JOIN: all right rows + matching left (rarely needed; swap tables for LEFT JOIN).

  • FULL OUTER JOIN: all rows from both tables; NULLs where no match.

  • SELF JOIN: table joined to itself; requires aliases; for hierarchies and comparisons.

  • CROSS JOIN: Cartesian product n×m rows; use with caution.

  • LEFT JOIN + WHERE on right table = INNER JOIN (common mistake). Use ON clause filter.

  • Join performance: index foreign keys; check work_mem for hash joins.

Want a visual for this concept?

Generate a diagram tailored to “JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to Subqueries, EXISTS & Correlated Subqueries →← Back to all SQL chapters