08 — JOINs: Stitching Tables Together

"Which join do I pick" was a guessing game every time, until the one rule underneath showed up. Writing it down collapsed it: a JOIN's type decides which unmatched rows survive, and the ON clause decides what counts as a match. [1][2]

The framing that clicked is the Venn-diagram picture, taken literally. Two tables are two sets of rows. ON table_a.key = table_b.key defines where the sets overlap. The join type then decides what to keep: only the overlap (INNER), the left set plus the overlap (LEFT), the right set plus the overlap (RIGHT), or everything from both (FULL OUTER). That's the entire decision — match rule in ON, keep rule in the type.

The four core joins

The decision is always which unmatched rows to keep [1]:

• INNER JOIN — keep only rows that match in both tables. Unmatched rows on either side are dropped [3].
• LEFT JOIN — keep every row from the left table; where there's no match on the right, pad the right columns with NULL [4].
• RIGHT JOIN — the mirror: every row from the right table, NULL-padding the left where unmatched [5].
• FULL OUTER JOIN — keep everything: matched rows plus unmatched rows from both sides, NULL-padded either way [6].

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

That LEFT JOIN returns every customer, whether or not they placed an order — customers with no orders get NULL in o.total. That's exactly how I answer "which customers have never ordered": the same LEFT JOIN plus WHERE o.id IS NULL.

Self join — a table joined to itself

A self join joins a table to itself, which sounds odd until there's hierarchy in the data — an employees table with a manager_id pointing back at employees [7][8]:

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

I alias the table twice (e and m) so the engine treats them as two inputs. Self joins are the standard tool for parent/child relationships stored in one table: org charts, category trees, threaded comments.

Cross join — every combination

A cross join pairs every row of the first table with every row of the second — the Cartesian product, rows_in_A × rows_in_B [9]. There's no ON clause because everything matches everything.

SELECT sizes.name, colors.name FROM sizes CROSS JOIN colors;

This is what I want when generating combinations (every size × every color), and what I accidentally get when I forget a join condition in an older-style FROM a, b query. Cross joins explode quickly — 1,000 × 1,000 = a million rows — so I use them deliberately, never by accident.

A note on the numeric functions parked under JOINs

The roadmap also lists the numeric functions — FLOOR, CEILING, ROUND, ABS, MOD — adjacent to joins, and the tie-in is worth a line. These are scalar functions I apply to columns in the SELECT list or WHERE, often right after a join produces a computed column [10][11][12][13][14]:

• FLOOR(x) rounds down; CEILING(x) rounds up; ROUND(x, n) rounds to n decimals.
• ABS(x) strips the sign; MOD(a, b) returns the remainder (a % b in many dialects).

SELECT FLOOR(total) AS whole_dollars,
       ROUND(total, 2) AS cents,
       ABS(balance) AS magnitude
FROM orders;

They belong to the broader family of value-transforming functions covered next; I note them here only because the roadmap nests them under JOIN queries.

How I use this

The habit I keep is the survival check: before I write a join, I ask which rows must survive when there's no match. If unmatched rows on the left matter (every customer, including those with no orders), it's a LEFT JOIN. If only matches matter, it's INNER. If I need gaps on both sides — reconciliation, finding orphans in either direction — it's FULL OUTER. Pinning the type to the survival requirement, rather than guessing, is what stops the silent row-loss bug where an INNER JOIN quietly drops the customers I actually wanted to see.

References

[1] DataCamp, "SQL JOINs Cheat Sheet," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/cheat-sheet/sql-joins-cheat-sheet

[2] YouTube, "SQL JOINs Tutorial for beginners," 2023. [Online]. Available: https://www.youtube.com/watch?v=0OQJDd3QqQM

[3] Programiz, "SQL INNER JOIN Clause," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/inner-join

[4] YouTube, "SQL LEFT JOIN - SQL Tutorial," 2023. [Online]. Available: https://www.youtube.com/watch?v=giKwmtsz1U8

[5] Programiz, "SQL RIGHT JOIN With Examples," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/right-join

[6] YouTube, "SQL FULL OUTER JOIN," 2023. [Online]. Available: https://www.youtube.com/watch?v=XpBkXo3DCEg

[7] DbVisualizer, "Understanding the Self Joins in SQL," dbvis.com, 2024. [Online]. Available: https://www.dbvis.com/thetable/understanding-self-joins-in-sql/

[8] W3Schools, "SQL self joins," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/sql_join_self.asp

[9] SQLShack, "SQL CROSS JOIN With Examples," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/sql-cross-join-with-examples/

[10] YouTube, "How to Round in SQL," 2023. [Online]. Available: https://www.youtube.com/watch?v=AUXw2JRwCFY

[11] W3Schools, "ABS," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/func_sqlserver_abs.asp

[12] YouTube, "MOD Function in SQL," 2023. [Online]. Available: https://www.youtube.com/watch?v=f1Rqf7CwjE0

[13] W3Schools, "SQL CEILING," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/func_sqlserver_ceiling.asp

[14] DataCamp, "What is the SQL ROUND Function and how does it work?," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/mastering-sql-round