Everyone learns joins from Venn diagrams and then writes a query that returns 4,000 rows from two tables of 200. The diagrams aren't wrong, they're just the wrong mental model: a join isn't set overlap, it's row matching. Think in row counts and joins stop being mysterious.

Two tables to work with — departments (4 rows) and employees (9 rows, one of them with department_id IS NULL because they haven't been assigned yet).

INNER JOIN — only rows that match on both sides

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.department_id;

Returns 8 rows, not 9. The unassigned employee has no matching department, so INNER drops them. This is the single most common source of "my report is missing people": an INNER JOIN quietly deleting rows you needed to count.

LEFT JOIN — every row from the left table, matched or not

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id;

Returns 9 rows. The unassigned employee is kept, with NULL in every column that came from departments. Rule of thumb: if the question says "all X, along with their Y (if any)", it's a LEFT JOIN.

RIGHT JOIN is the mirror image — every row from the right table. In practice almost nobody writes one; swapping the table order and using LEFT reads better, and some engines (SQLite among them, historically) didn't support RIGHT at all.

FULL OUTER JOIN — everything from both sides

Keeps unmatched rows from both tables, padding the other side with NULL. Useful for reconciliation ("what's in one system but not the other"). Where it isn't supported, the standard workaround is a LEFT JOIN UNION-ed with a RIGHT-style one.

CROSS JOIN — every combination

4 departments × 9 employees = 36 rows. Deliberately useful for generating grids (every student × every exam). Accidentally, it's what you get when you forget the ON condition — and it's why a "simple" query suddenly returns thousands of rows.

The three traps that actually bite

1. Filtering a LEFT JOIN in WHERE turns it into an INNER JOIN

-- Wrong: silently drops the unmatched rows you just protected
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.city = 'Bengaluru';

-- Right: the condition belongs in the join
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
  ON d.id = e.department_id AND d.city = 'Bengaluru';

Because NULL = 'Bengaluru' is never true, the WHERE clause filters out exactly the unmatched rows the LEFT JOIN preserved. Conditions on the right-hand table go in ON; conditions on the left-hand table go in WHERE.

2. Duplicate rows from a one-to-many join

Join orders to order_items and each order appears once per item. Now SUM(orders.total) is inflated — you're adding the same order total several times. Aggregate the many-side first (in a subquery or CTE), then join the result.

3. NULL never equals anything

NULL = NULL is not true, it's unknown. Use IS NULL / IS NOT NULL, and remember COUNT(column) skips NULLs while COUNT(*) counts rows.

How to debug a join that returns too many rows

Run each table separately with its filters and note the row counts.
Run the join with no aggregation and LIMIT 20. Look at actual rows — duplicates are usually obvious on sight.
Check whether the join key is unique on at least one side: SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1;
Only add GROUP BY and aggregates once the raw joined rows look right. Aggregating a wrong join just hides the bug behind a plausible number.

Before you run a join, predict the row count out loud. When the actual count differs, you've learned something specific instead of guessing.

The fastest way to internalise this is to run the same query with INNER and then LEFT against a real table and watch the count change. You can do that in a browser tab — see practising SQL online — and once joins are solid, indexes and query performance are the natural next step.

SQL Joins Explained: INNER, LEFT, RIGHT and FULL (With Row Counts)