Visual reference for all SQL JOIN types — INNER, LEFT, RIGHT, FULL OUTER, CROSS and SELF joins with syntax and examples.
| JOIN Type | Returns | Syntax | Use When |
|---|---|---|---|
| INNER JOIN | Matching rows in BOTH tables | FROM a INNER JOIN b ON a.id = b.id | You only want rows with matches on both sides |
| LEFT JOIN | All left rows + matching right | FROM a LEFT JOIN b ON a.id = b.id | Keep all left rows, even with no match |
| RIGHT JOIN | All right rows + matching left | FROM a RIGHT JOIN b ON a.id = b.id | Keep all right rows, even with no match |
| FULL OUTER JOIN | All rows from both tables | FROM a FULL OUTER JOIN b ON a.id = b.id | Keep everything, NULL where no match |
| CROSS JOIN | Cartesian product | FROM a CROSS JOIN b | Every combination of rows |
| SELF JOIN | Table joined to itself | FROM a t1 JOIN a t2 ON t1.id = t2.ref | Hierarchical data, org charts |
| Scenario | INNER JOIN | LEFT JOIN | FULL OUTER JOIN |
|---|---|---|---|
| Row in left only | ❌ Excluded | ✅ Included (NULLs for right) | ✅ Included (NULLs for right) |
| Row in right only | ❌ Excluded | ❌ Excluded | ✅ Included (NULLs for left) |
| Match in both | ✅ Included | ✅ Included | ✅ Included |
| NULL join key | ❌ Never matches | ❌ Never matches | ❌ Never matches |
| Tip | Why It Matters |
|---|---|
| Index join columns (ON a.id = b.id) | Without index, every join is a full table scan |
| JOIN before WHERE when possible | Reduces rows early, less work for WHERE |
| Avoid CROSS JOIN on large tables | N×M rows — grows quadratically |
| Use INNER JOIN over subqueries | Optimizer handles joins better than correlated subqueries |
| EXPLAIN/EXPLAIN ANALYZE your joins | See if index is being used |
| Pattern | SQL | Use Case |
|---|---|---|
| Find orphaned rows | WHERE b.id IS NULL (after LEFT JOIN) | Rows in A with no matching B |
| Exclude matches | WHERE b.id IS NULL (after LEFT JOIN) | Anti-join pattern |
| Count with join | SELECT COUNT(DISTINCT a.id) | Avoid inflation from 1:many joins |
| Multiple joins | JOIN c ON b.id = c.b_id | Chain multiple tables |
| Function | Purpose | Example |
|---|---|---|
| ROW_NUMBER() | Unique sequential rank (no ties) | ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) |
| RANK() | Rank with gaps on ties (1,1,3) | RANK() OVER (ORDER BY score DESC) |
| DENSE_RANK() | Rank without gaps on ties (1,1,2) | DENSE_RANK() OVER (ORDER BY score DESC) |
| LAG(col, n) | Value from n rows before current | LAG(revenue,1) OVER (ORDER BY month) — previous month |
| LEAD(col, n) | Value from n rows after current | LEAD(revenue,1) OVER (ORDER BY month) — next month |
| SUM() OVER () | Running total without collapsing rows | SUM(sales) OVER (PARTITION BY region ORDER BY date) |
| NTILE(n) | Divide rows into n equal buckets | NTILE(4) OVER (ORDER BY score) — quartiles |
| FIRST_VALUE / LAST_VALUE | First/last value in window frame | FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date) |
| Scenario | Use | Reason |
|---|---|---|
| Filter by aggregate result | Subquery (WHERE salary > (SELECT AVG...)) | Aggregate not available in same WHERE clause |
| Combine columns from two tables | JOIN | Optimizer handles JOIN better; subquery re-runs per row in correlated case |
| EXISTS check (semi-join) | EXISTS subquery | Stops at first match — faster than JOIN + DISTINCT |
| NOT EXISTS | NOT EXISTS subquery | Handles NULLs correctly — NOT IN fails if subquery has NULLs |
| Top-N per group | Subquery or CTE + ROW_NUMBER() | JOIN approach is verbose; window function cleaner |
| Reuse intermediate result | CTE (WITH clause) | Readable, optimizer may materialise once; cleaner than nested subquery |
| Correlated subquery (row-by-row) | Avoid — use JOIN or window fn | Correlated subquery runs once per outer row — O(n²) |