the mental model

Every join type starts from the same place: form every combination of rows from both tables (the cross join), then keep only the combinations that satisfy the join condition. What differs between join types is what happens to rows that don't satisfy the condition — whether they're dropped, or kept with NULLs filled in for the missing side.

INNER JOIN

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- only orders that have a matching customer, and only customers that have a matching order
The default if you just write JOIN with no qualifier. Rows on either side with no match are dropped entirely.

LEFT JOIN (and RIGHT JOIN)

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- every customer, even ones with zero orders — order_id is NULL for those rows
LEFT JOIN keeps every row from the left table regardless of a match; unmatched right-side columns come back NULL. RIGHT JOIN is the mirror image and rarely used in practice — swapping table order and using LEFT JOIN is more common style.

FULL JOIN and CROSS JOIN

-- FULL JOIN: every row from both sides, NULLs wherever there's no match on the other side
SELECT c.name, o.id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id;

-- CROSS JOIN: every combination, no condition — n rows × m rows
SELECT size.label, color.label
FROM sizes size
CROSS JOIN colors color;
-- generating every size/color combination for a product catalog is a legitimate use

self joins

-- find employees and their manager's name, both from the same 'employees' table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
A self join is just a normal join where both sides happen to be the same table — the table aliases (e, m) are what make it possible to tell the two "copies" apart in the query.

the fan-out trap

Joining a one-to-many relationship multiplies rows — a customer with 3 orders and 2 support tickets, joined to both tables at once, produces 6 rows (3×2), not 5. Any SUM() or COUNT() computed after that join is silently wrong, often by a lot, with no error to warn you.
-- WRONG: total_spent is inflated by however many tickets each customer has
SELECT c.id, SUM(o.amount) AS total_spent, COUNT(t.id) AS ticket_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN tickets t ON t.customer_id = c.id
GROUP BY c.id;

-- FIX: aggregate each side separately before joining, or use a subquery per metric
SELECT c.id, o.total_spent, t.ticket_count
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id) o ON o.customer_id = c.id
LEFT JOIN (SELECT customer_id, COUNT(*) AS ticket_count FROM tickets GROUP BY customer_id) t ON t.customer_id = c.id;
This is the single most common source of subtly wrong dashboard numbers. If an aggregate looks too high after adding a join, suspect fan-out first.

ON vs USING

... ON o.customer_id = c.id      -- general form, columns can have different names
... USING (customer_id)          -- shorthand when both tables use the same column name

where to go from here

Subqueries & CTEs — the subquery-per-metric pattern used above, formalized.
Aggregation & GROUP BY — what actually happens to duplicated rows in a GROUP BY.