Joins: INNER, LEFT, RIGHT, FULL, CROSS

Normalize for correct writes; JOINs at scale pay in scatter-gather, disk-deep index walks, network-heavy intermediates, and uncacheable per-user results.

Over a LEFT JOIN, count the child's key column – `COUNT(child.id)` – never `COUNT(*)`, because the placeholder NULL row of a childless parent still counts as one.

JOIN employees to departments, GROUP BY department, COUNT the rows – and use LEFT JOIN with `COUNT(emp_id)` to show empty departments as zero.

Compute the group aggregate once in a CTE, then JOIN – never recompute per row.

LATERAL is a FROM-clause subquery that can see the row to its left, so it runs per row – perfect for top-N per group.

A database JOIN matches inside one engine; an application JOIN fetches each piece by primary key from the best store and assembles in code – cheap, cacheable point lookups that compose.

JOIN combines columns from related rows horizontally by a match condition; UNION stacks rows from compatible queries vertically.

INNER keeps matches only, LEFT and RIGHT keep all of one side, FULL keeps all of both, CROSS pairs everything with everything, and SELF joins a table to itself.

NATURAL JOIN guesses the match and is risky, CROSS JOIN pairs everything with everything, and ROLLUP adds subtotals plus a grand total to a GROUP BY.

Anti-join = `LEFT JOIN ... WHERE right.key IS NULL` or `NOT EXISTS`; and any filter on the right table must go in `ON`, not `WHERE`, or it silently turns the outer join into an inner join.

Self-join for manager comparison, correlated subquery or DENSE_RANK for Nth-highest, CASE for pivot, gaps-and-islands for gaps and streaks, SUM OVER for running totals, and ROW_NUMBER to de-duplicate.

For "does a related row exist?", use `EXISTS` – it returns each row once and short-circuits, while an INNER JOIN duplicates rows and needs a DISTINCT to clean up.