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 |