--- title: "08 — JOINs: Stitching Tables Together" uid: joins tags: ["sql", "inner-join", "roadmap:sql", "self-join", "right-join", "full-outer-join", "left-join", "cross-join", "joins"] excerpt: "A JOIN's type decides which unmatched rows survive; the ON clause decides what counts as a match. One rule collapses the join guessing game." date: 2026-08-13T03:27:37+0000 source: https://www.aveshina.my.id/en/blog/joins --- "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. ```figure ``` ## 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](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](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](https://www.programiz.com/sql/inner-join) [4] YouTube, "SQL LEFT JOIN - SQL Tutorial," 2023. [Online]. Available: [https://www.youtube.com/watch?v=giKwmtsz1U8](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](https://www.programiz.com/sql/right-join) [6] YouTube, "SQL FULL OUTER JOIN," 2023. [Online]. Available: [https://www.youtube.com/watch?v=XpBkXo3DCEg](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/](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](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/](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](https://www.youtube.com/watch?v=AUXw2JRwCFY) [11] W3Schools, "ABS," w3schools.com, 2024. [Online]. Available: [https://www.w3schools.com/sql/func_sqlserver_abs.asp](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](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](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](https://www.datacamp.com/tutorial/mastering-sql-round) ```quiz Q: You need every customer, including those with no orders. Which join? - INNER JOIN orders - LEFT JOIN orders correct: 1 explain: LEFT JOIN keeps all rows from the left table (customers), padding the right (orders) with NULLs where there's no match. INNER JOIN would drop customers with no orders. Q: What does a CROSS JOIN produce? - Only rows that match in both tables - Every combination — rows_in_A × rows_in_B correct: 1 explain: A CROSS JOIN is the Cartesian product: each row of the first table paired with each row of the second. No ON clause is used. Q: A self join is used to… - join a table to itself, typically to walk a hierarchy like manager_id → employee - join three unrelated tables together correct: 0 explain: A self join aliases one table twice and joins it to itself, the standard tool for parent/child relationships stored in a single table. Q: Which join keeps unmatched rows from BOTH tables, NULL-padding either side? - FULL OUTER JOIN - INNER JOIN correct: 0 explain: FULL OUTER JOIN returns matched rows plus unmatched rows from both sides, with NULLs where data is missing. INNER drops all unmatched rows. Q: FLOOR(7.9) returns… - 8 - 7 correct: 1 explain: FLOOR rounds down to the largest integer less than or equal to the input, so FLOOR(7.9) is 7. CEILING(7.1) would be 8. ```