JOINs

Combining rows from two tables

A JOIN stitches tables together horizontally, matching rows by a related column (usually a foreign key). Relational databases split data across tables to avoid duplication; JOINs put it back together for analysis.

The four join types

-- Orders + customers; only matched rows on both sides
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- Keep ALL orders even if the customer is missing
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

• INNER JOIN — only rows that match in both tables.
• LEFT JOIN — every row from the left table; NULLs where the right has no match.
• RIGHT JOIN — every row from the right table; NULLs where the left has no match.
• FULL OUTER JOIN — every row from both; NULLs on either side where there's no match.

A LEFT JOIN is the workhorse of analytics: it guarantees you keep your base table's rows, and NULLs on the right instantly reveal missing relationships.

Self-joins

A table can be joined to itself by giving it two aliases. This is how you model hierarchies like manager → employee or category → parent:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

The ON clause is a condition

ON defines what makes a row "match". It can include extra filters, though it's cleaner to put non-join conditions in WHERE:

-- Prefer this: join condition in ON, filters in WHERE
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE o.total > 100;