What Are Joins?
Linking Relational Tables Without Losing Your Mind
LinkedIn Hook
"You normalized your data into 12 tables. Now you cannot answer a single business question with one query."
Every developer hits the same wall on day one of working with relational databases. You learn that data should be split across tables -- customers in one place, orders in another, products in a third, addresses in a fourth -- so nothing is duplicated and nothing goes stale. You diligently design five clean tables for your e-commerce app. Then your product manager asks "show me the email of every customer who bought a red item this month" and you realize that email lives in one table, the order in another, the line items in a third, the products in a fourth, and the colors in a fifth. None of them have all the columns you need. So how do you actually answer the question?
The answer is the JOIN. A join is not a separate kind of query -- it is the mechanism the relational model uses to stitch related rows back together at read time based on the foreign keys you put in place when you normalized the data. Joins are why splitting the data into clean tables is even possible. Without joins, normalization would be a one-way trip into a swamp of disconnected fragments.
But joins are also the place where most beginner SQL goes wrong. People copy-paste an INNER JOIN they once saw work, get confused when rows mysteriously disappear, switch to a LEFT JOIN to "fix" it, then watch their result count explode by 10x because two of the joined tables had a one-to-many relationship they did not understand. The query "works" but returns 47,000 rows instead of 5,000. The dashboard ships. The numbers are wrong. Nobody notices for two weeks.
Before you learn INNER, LEFT, RIGHT, FULL, CROSS, and SELF joins as separate recipes, you need the mental model. What is a join actually doing? What is the relationship between a foreign key and a join condition? Why are tables split in the first place, and what is the price you pay to put them back together?
In Lesson 5.1, I walk through joins from first principles: the relational model, normalization, foreign keys, the conceptual cartesian product underneath every join, and the join condition that filters it back down to the rows you actually want. By the end you will read every later lesson (INNER, LEFT, RIGHT, FULL, CROSS, SELF) as variations on a single idea instead of as six separate spells.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #MySQL #BackendDevelopment #InterviewPrep #DataModeling
What You'll Learn
- Why relational databases split data into multiple tables in the first place (normalization, single source of truth)
- What a foreign key is and why it is the anchor that makes joins possible
- The mental model of a join as "cartesian product filtered by a join condition"
- How a JOIN ... ON clause matches rows from two tables row-by-row
- The difference between the join condition (ON) and the row filter (WHERE) and why the order matters
- A preview of the six join flavors -- INNER, LEFT, RIGHT, FULL, CROSS, SELF -- and how they are all variations on a single idea
- Cardinality basics: one-to-one, one-to-many, and many-to-many, and how each shapes your join results
- Why a join that "works" can still silently return the wrong number of rows, and how to spot it
The Two Filing Cabinets Analogy — Why Data Lives in Pieces
Imagine running a small clinic. You keep two metal filing cabinets in the back office. The first cabinet is labeled PATIENTS and holds one folder per person -- name, date of birth, phone number, insurance ID, allergy notes. The second cabinet is labeled VISITS and holds one folder per appointment -- date, doctor seen, diagnosis, prescription, follow-up date.
Why two cabinets and not one? Because a single patient comes in many times. If you stored all the patient information together with each visit, every visit folder would repeat the same name, date of birth, phone number, and allergy notes. When a patient changes their phone number, you would have to find every one of their visit folders and update each one. Miss one, and now your records disagree -- the same patient has two phone numbers depending on which folder you look at. That is exactly the bug that destroys real-world data quality. Splitting into two cabinets means each fact lives in exactly one place: the patient's name lives in the PATIENTS cabinet, the visit's diagnosis lives in the VISITS cabinet, and neither one duplicates the other.
But splitting creates a new problem: how do you know which visits belong to which patient? You write the patient's ID on the front of every visit folder. The patient ID is the link -- the thread that ties a visit folder back to its owner in the other cabinet. When the doctor asks "show me Maria's visit history," the receptionist finds Maria in the PATIENTS cabinet, reads her ID, then walks to the VISITS cabinet and pulls every folder with that ID written on it. The act of "walking from one cabinet to the other and pulling the matching folders" is exactly what a SQL JOIN does.
+---------------------------------------------------------------+
| THE TWO FILING CABINETS |
+---------------------------------------------------------------+
| |
| PATIENTS cabinet VISITS cabinet |
| +-------------+ +----------------+ |
| | id: 101 | <----------- | patient_id:101 | |
| | name: Maria | | date: 2026-01 | |
| | dob: 1989 | | dx: flu | |
| +-------------+ +----------------+ |
| |
| +-------------+ +----------------+ |
| | id: 102 | <----+ | patient_id:101 | |
| | name: Jamal | | | date: 2026-03 | |
| | dob: 1992 | | | dx: sprain | |
| +-------------+ | +----------------+ |
| | |
| +------ +----------------+ |
| | patient_id:102 | |
| | date: 2026-02 | |
| | dx: checkup | |
| +----------------+ |
| |
| The arrow from a VISITS folder back to a PATIENTS folder |
| is the foreign key. The act of following it is the JOIN. |
| |
+---------------------------------------------------------------+Hold this picture in your head for the rest of the chapter. Every join, no matter how exotic the keyword, is doing one thing: walking arrows between cabinets to pull related rows back together.
Why Tables Are Split — Normalization in One Paragraph
The principle that drives a relational schema into multiple tables is called normalization. The short version: every fact should live in exactly one place. A customer's email belongs in the customers table and nowhere else. An order's total belongs in the orders table and nowhere else. A product's price belongs in products. Anything that needs to refer to a customer, an order, or a product points to the row in the right table using a foreign key, instead of copying the data.
The three benefits are concrete: no update anomalies (changing a customer email touches one row, not thousands of order rows that copied it), no insertion anomalies (you can record a new customer before they have placed any orders), and no deletion anomalies (deleting an order does not accidentally erase the only copy of that customer's email).
The cost is exactly what this lesson is about: any question that crosses table boundaries needs a JOIN to put the pieces back together. That cost is small once you understand joins, and it is dwarfed by the cost of fixing a denormalized schema in production after the data has gone bad.
Foreign Keys — The Glue
A foreign key is a column in one table whose values must match the primary key of a row in another table. It is a promise enforced by the database: "every value in orders.customer_id is a real customers.id." If you try to insert an order with customer_id = 999 and there is no customer with id 999, the database refuses the insert. If you try to delete customer 42 while orders still reference them, the database either refuses the delete or cascades it, depending on how you defined the constraint.
-- Sample schema we will use throughout this chapter.
-- Two tables linked by a foreign key.
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total NUMERIC(10, 2) NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Seed data
INSERT INTO customers (name, email, city) VALUES
('Alice', 'alice@example.com', 'Berlin'),
('Bob', 'bob@example.com', 'Paris'),
('Carol', 'carol@example.com', 'Berlin'),
('Dave', 'dave@example.com', 'Madrid');
INSERT INTO orders (customer_id, total, status) VALUES
(1, 120.00, 'shipped'), -- Alice
(1, 55.50, 'pending'), -- Alice again
(2, 300.00, 'shipped'), -- Bob
(3, 42.00, 'shipped'); -- Carol
-- Dave (id=4) has no orders yet.Two important observations from this seed data, because they will drive every join example below:
- Alice has two orders. She is one customer mapped to many orders -- a classic one-to-many relationship.
- Dave has zero orders. When we join, his row will only appear in the result if we use a join flavor (LEFT) that preserves unmatched rows. With a plain INNER JOIN, Dave silently disappears.
Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Two table diagrams side by side. LEFT table 'customers' with rows id 1-4 (Alice, Bob, Carol, Dave). RIGHT table 'orders' with 4 rows showing customer_id values 1, 1, 2, 3. Sky blue (#4fc3f7) arrows draw from each orders.customer_id back to its matching customers.id. Dave (id=4) has no incoming arrow and is highlighted in rose (#ff5c8a) with the label 'no matching orders'. White monospace labels throughout. Title: 'Foreign Key = the Glue'."
The Conceptual Model — Cartesian Product, Then Filter
Here is the secret that makes every join click into place. A JOIN is conceptually a two-step operation: first the database pretends to build the cartesian product of the two tables (every row from the left paired with every row from the right), then it applies the join condition (the ON clause) to keep only the pairs where the condition is true.
The database does not actually materialize the entire cartesian product in memory -- that would be catastrophically slow on real data. Instead, the planner uses indexes and join algorithms (nested loop, hash join, merge join) to compute the result efficiently. But the logical meaning of the join is exactly "cartesian product, then filter." If you ever get confused about what a join is going to return, mentally expand it to the cartesian product and apply the ON clause. You will always get the right answer.
+---------------------------------------------------------------+
| STEP 1 - CONCEPTUAL CARTESIAN PRODUCT |
+---------------------------------------------------------------+
| |
| customers (4 rows) X orders (4 rows) = 16 candidate pairs |
| |
| Alice + order(cust 1, 120) <- KEEP (1 = 1) |
| Alice + order(cust 1, 55.5) <- KEEP (1 = 1) |
| Alice + order(cust 2, 300) <- DROP (1 != 2) |
| Alice + order(cust 3, 42) <- DROP (1 != 3) |
| Bob + order(cust 1, 120) <- DROP (2 != 1) |
| Bob + order(cust 1, 55.5) <- DROP (2 != 1) |
| Bob + order(cust 2, 300) <- KEEP (2 = 2) |
| Bob + order(cust 3, 42) <- DROP (2 != 3) |
| Carol + order(cust 1, 120) <- DROP (3 != 1) |
| Carol + order(cust 1, 55.5) <- DROP (3 != 1) |
| Carol + order(cust 2, 300) <- DROP (3 != 2) |
| Carol + order(cust 3, 42) <- KEEP (3 = 3) |
| Dave + order(cust 1, 120) <- DROP (4 != 1) |
| Dave + order(cust 1, 55.5) <- DROP (4 != 1) |
| Dave + order(cust 2, 300) <- DROP (4 != 2) |
| Dave + order(cust 3, 42) <- DROP (4 != 3) |
| |
| 4 pairs survive. That is the INNER JOIN result. |
| |
+---------------------------------------------------------------+-- The query that produces those 4 surviving pairs.
-- Read this as: "for every pair where customers.id = orders.customer_id,
-- return one row containing columns from both tables."
SELECT c.name, o.total, o.status
FROM customers c
JOIN orders o ON c.id = o.customer_id;
-- name | total | status
-- ------+--------+---------
-- Alice | 120.00 | shipped
-- Alice | 55.50 | pending
-- Bob | 300.00 | shipped
-- Carol | 42.00 | shipped
-- Note: Dave does NOT appear. He had no matching order, and INNER JOIN
-- drops anyone whose join condition never matched.The two-step model also explains why a missing or wrong ON clause can be catastrophic. If you forget the ON clause entirely (or write ON 1=1), you ask for the cartesian product itself -- every row paired with every row. Joining a 1,000-row table with another 1,000-row table that way produces 1,000,000 rows. This is the infamous "accidental cross join" and it is how juniors take down staging databases.
Reading a JOIN Statement Out Loud
Let me show you how to read a JOIN clause the way it is actually meant to be read, so the syntax stops feeling like incantation.
SELECT c.name, -- columns we want in the output
o.total
FROM customers c -- start with the customers table, alias it 'c'
JOIN orders o -- bring in the orders table, alias it 'o'
ON c.id = o.customer_id -- match a customer to their orders
WHERE o.status = 'shipped'; -- then filter the matched pairsRead out loud: "Take the customers table. For each customer, bring in every orders row where the order's customer_id equals the customer's id. From those pairs, keep only the ones where the order's status is 'shipped'. Return the customer's name and the order's total."
Three things to notice:
- Aliases (
c,o). Once a table is aliased in the FROM/JOIN clause, you can refer to its columns with the short alias instead of the full table name. This is essential when the same column name (id) exists in both tables -- without qualification, the database does not know whichidyou mean. - The ON clause is the matching rule, not a filter. Conceptually it decides which row pairs survive the join. WHERE is then applied to the surviving pairs.
- The order of FROM and JOIN matters for readability, not for results.
customers c JOIN orders oandorders o JOIN customers cproduce the same rows for an INNER JOIN -- the join is symmetric. (For LEFT and RIGHT joins, order absolutely matters, as you will see in lessons 5.3 and 5.4.)
ON vs WHERE — Same Result for INNER JOIN, Different for OUTER
A common source of confusion: can you put your filtering logic in the ON clause, the WHERE clause, or both? For an INNER JOIN, the answer is "either works and the result is the same." For OUTER joins (LEFT, RIGHT, FULL), the answer is "they mean very different things and you must use ON for join conditions and WHERE for post-join filters." Get this wrong and your LEFT JOIN silently degrades into an INNER JOIN.
-- Inner join: ON and WHERE are interchangeable for additional conditions.
-- These two queries return identical results.
SELECT c.name, o.total
FROM customers c
JOIN orders o
ON c.id = o.customer_id
AND o.status = 'shipped';
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'shipped';
-- Both return:
-- name | total
-- ------+--------
-- Alice | 120.00
-- Bob | 300.00
-- Carol | 42.00For inner joins, prefer the second form: put the join key in ON, put row filters in WHERE. It reads more naturally and matches what most teams write. For outer joins, the distinction is structural -- we will return to it in lessons 5.3 and 5.4.
A Tour of the Six Join Flavors
Here is the preview of the chapter, so the names stop feeling random. Every flavor below is built on the same "cartesian product, then filter" idea -- the only thing that changes is what happens to rows that have no match on the other side.
+---------------------------------------------------------------+
| THE SIX JOIN FLAVORS |
+---------------------------------------------------------------+
| |
| INNER JOIN |
| Keep only pairs where the ON condition is true. |
| Unmatched rows from BOTH sides are dropped. |
| (5.2) |
| |
| LEFT [OUTER] JOIN |
| Keep all rows from the LEFT table. |
| For unmatched left rows, fill the right columns with NULL. |
| (5.3) |
| |
| RIGHT [OUTER] JOIN |
| Mirror image of LEFT. Keep all rows from the RIGHT table. |
| (5.4) |
| |
| FULL [OUTER] JOIN |
| Keep all rows from BOTH tables. Unmatched sides get NULL. |
| (5.5) |
| |
| CROSS JOIN |
| The literal cartesian product. No ON clause. |
| Every left row paired with every right row. |
| (5.6) |
| |
| SELF JOIN |
| A table joined to itself, usually with two aliases. |
| Used for hierarchies, sequences, and pairs. |
| (5.7) |
| |
+---------------------------------------------------------------++---------------------------------------------------------------+
| VENN-STYLE MENTAL MODEL |
+---------------------------------------------------------------+
| |
| L = left table R = right table |
| M = matched rows U_L = unmatched left U_R = unmatched |
| right |
| |
| INNER -> M |
| LEFT -> M + U_L (with NULLs on right) |
| RIGHT -> M + U_R (with NULLs on left) |
| FULL -> M + U_L + U_R |
| CROSS -> L X R (no condition at all) |
| |
+---------------------------------------------------------------+You will spend far more time writing INNER and LEFT joins than the other four combined. INNER is the default workhorse. LEFT is the answer to "I want the customer even if they have no orders, and a NULL where the order info would go."
Cardinality — The Quiet Killer
Before moving on, you must understand cardinality, because it is the most common source of subtle join bugs. Cardinality describes how many rows on one side of a relationship can correspond to rows on the other side.
One-to-one (1:1). Every row on the left has at most one matching row on the right, and vice versa. Example: a users table and a user_profiles table where each user has exactly one profile. Joining 100 users to 100 profiles gives you 100 rows.
One-to-many (1:N). A single row on the left can correspond to many rows on the right. Example: one customers row and many orders rows. Joining 100 customers (averaging 5 orders each) to the orders table gives you 500 rows -- because each customer row gets duplicated once per matching order. This is normal and correct, but it surprises beginners.
Many-to-many (M:N). Both sides can have many matches. You cannot model this with a single foreign key -- you need a join table (also called a junction or bridge table). Example: students and courses, with an enrollments join table. To answer "which courses is each student taking," you join students -> enrollments -> courses (a three-table join, covered in lesson 5.8). If you naively join students directly to courses you have nothing to join on.
-- Demonstrating one-to-many row multiplication.
-- Alice has 2 orders, so a SUM over a naive join double-counts her totals.
SELECT c.name, c.email, SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email;
-- name | email | total_spent
-- ------+-------------------+-------------
-- Alice | alice@example.com | 175.50 -- 120 + 55.50, correct
-- Bob | bob@example.com | 300.00
-- Carol | carol@example.com | 42.00
-- But beware! If you also join an unrelated 'shipping_addresses' table
-- where Alice has 3 saved addresses, the cartesian effect kicks in:
-- her 2 orders get multiplied by her 3 addresses = 6 rows for Alice,
-- and SUM(o.total) silently becomes 175.50 * 3 = 526.50. Wrong!
-- Lesson 5.10 covers this trap in detail.The takeaway for this lesson: always know the cardinality of the relationship before you join. If you joined two one-to-many relationships in the same query, your result rows are multiplied and any aggregates are inflated. The fix is usually to aggregate one side in a subquery before joining, or to use DISTINCT carefully, or to restructure the query as multiple joins -- all techniques covered later in the chapter.
Common Mistakes
1. Forgetting the ON clause and producing a cartesian product.
Writing SELECT * FROM customers, orders (or FROM customers JOIN orders without ON) asks the database for every customer paired with every order -- a true cross join. On a 10k customers x 1M orders setup, that is 10 billion rows. Your query never returns, your database CPU pegs at 100%, and your DBA pages you. Always include an explicit ON clause unless you genuinely want a CROSS JOIN (and if you do, write CROSS JOIN so the intent is loud).
2. Joining without qualifying ambiguous columns.
If both tables have an id column and you write SELECT id FROM customers c JOIN orders o ON c.id = o.customer_id, the database raises "column reference 'id' is ambiguous." Always alias your tables and qualify column names: c.id, o.id. It also makes the query readable to the next developer.
3. Using INNER JOIN when you needed LEFT JOIN.
A common bug: you join customers to orders to count orders per customer, and customers with zero orders silently disappear from the report. The fix is LEFT JOIN orders o ON c.id = o.customer_id plus COUNT(o.id) (not COUNT(*)), so missing matches show up as zero instead of being dropped. Knowing whether you need INNER or LEFT comes down to one question: "do I want to keep rows from the left table even when they have no match on the right?"
4. Putting filters on the right table in WHERE during a LEFT JOIN.
LEFT JOIN orders o ON c.id = o.customer_id WHERE o.status = 'shipped' looks correct but is not. The WHERE clause is applied after the join, and it eliminates every row where o.status is NULL -- which includes every customer who had no order in the first place. The LEFT JOIN silently turns into an INNER JOIN. The fix is to move the filter into the ON clause: LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped'.
5. Joining many-to-many tables without thinking about row multiplication.
If a customer has 2 orders and 3 addresses and you join all three tables in one query, each customer row appears 6 times. SUM(o.total) then overstates revenue by 3x. The fix is to either aggregate one side in a CTE or subquery before joining, or to break the question into two queries. Lesson 5.10 ("Common Pitfalls") covers this in depth.
Interview Questions
1. "Why do relational databases split data into multiple tables, and what role do joins play?"
Relational databases split data into multiple tables to follow the principle of normalization: every fact should live in exactly one place. If a customer's email lives only in the customers table, then changing the email touches one row, and there is no risk of two parts of the database disagreeing about that customer's email. Splitting also lets you record entities independently -- you can insert a new customer before they have placed any orders, and you can delete an order without losing the customer's contact information. The cost of this design is that any business question that crosses table boundaries requires reconnecting the related rows at read time, and that reconnection is exactly what a JOIN does. The join uses the foreign key (a column whose values must match a primary key in another table) to find which rows on each side belong together, and stitches them into combined result rows. Without joins, a normalized schema would be unusable for real queries -- joins are the read-time mechanism that makes write-time normalization possible.
2. "Explain the conceptual difference between an INNER JOIN and a LEFT JOIN."
An INNER JOIN keeps only the row pairs where the join condition is true. If a row on the left side has no matching row on the right, that row is dropped from the result entirely; the same applies to unmatched rows on the right. A LEFT JOIN keeps every row from the left table no matter what, and for left rows that had no match on the right it fills the right-side columns with NULL. Concretely: if you join customers to orders with an INNER JOIN and a customer has zero orders, that customer disappears from the result. The same query as a LEFT JOIN keeps the customer in the output with NULLs in the order columns. The choice between them is driven by intent: use INNER when you need both sides to have a match (e.g., "show me the total of every order with its customer's name") and LEFT when you need to preserve the left side even without matches (e.g., "show me every customer and their order count, including customers with zero orders").
3. "What is a foreign key, and is it required for joins to work?"
A foreign key is a constraint declared on a column (or set of columns) in one table that requires its values to match an existing primary key (or unique key) in another table. The database enforces the constraint at write time: it refuses to insert an order whose customer_id does not correspond to a real customer, and it refuses to delete a customer who still has referencing orders (unless you defined ON DELETE CASCADE). Foreign keys are about data integrity. Joins, however, do not technically require a foreign key -- you can write JOIN ... ON a.x = b.y between any two columns of compatible types, even if no constraint exists. The join just compares values. But foreign keys are important for joins for two practical reasons: they signal intent so other developers (and the query planner) understand the relationship, and they guarantee referential integrity so your join results are not contaminated by orphan rows pointing at deleted parents. Best practice is to always declare a foreign key when one logically exists, even if the application could enforce it.
4. "Walk me through the conceptual execution of a JOIN. What is the relationship to a cartesian product?"
Conceptually, a JOIN is a two-step operation. Step one: form the cartesian product of the two tables, which is every row from the left paired with every row from the right. If the left has 4 rows and the right has 4 rows, the cartesian product has 16 candidate pairs. Step two: apply the join condition (the ON clause) and keep only the pairs where the condition evaluates to true. With ON c.id = o.customer_id, only pairs where the customer's id matches the order's customer_id survive. This conceptual model is exact for an INNER JOIN, and OUTER JOINs add a step where unmatched rows from the preserved side(s) are added back with NULLs. In practice the database never materializes the full cartesian product -- the planner uses indexes and chooses among nested loop, hash join, and merge join algorithms to compute the result efficiently. But the logical meaning is what you should reason about when writing the query: imagine the cartesian product, then filter. That mental model never gives a wrong answer about what rows the join will return.
5. "Why is cardinality important when writing joins, and what kinds of bugs does it cause?"
Cardinality describes how many rows on one side of a relationship can correspond to rows on the other side: one-to-one, one-to-many, or many-to-many. It matters because joins multiply rows. If a single customer has five orders and you join customers to orders, that one customer row appears five times in the result -- which is correct, but surprising if you expected one row per customer. The bug shows up when you join a many-to-something relationship and then aggregate without realizing the multiplication. For example, joining customers to orders (one-to-many) and also to shipping_addresses (one-to-many) in the same query causes each customer to appear (orders x addresses) times, and SUM(orders.total) is silently inflated by a factor equal to the number of addresses. The fix is to know your cardinality up front, aggregate one side in a subquery or CTE before joining, use careful DISTINCT, or restructure the query as separate joins. Many-to-many relationships add another wrinkle: they cannot be joined directly and require a junction table (e.g., enrollments between students and courses). Always ask "how many rows on each side?" before writing a join, and always count the result rows after running it as a sanity check.
Quick Reference — Cheat Sheet
+---------------------------------------------------------------+
| JOINS - MENTAL MODEL CHEAT SHEET |
+---------------------------------------------------------------+
| |
| WHY JOINS EXIST: |
| Normalization splits data across tables. |
| Joins reconnect related rows at read time using FKs. |
| |
| THE FOREIGN KEY: |
| A column whose values must match a PK in another table. |
| The arrow that lets you walk between cabinets. |
| |
| THE TWO-STEP MENTAL MODEL: |
| 1. Form cartesian product (every left row x every right) |
| 2. Apply ON clause to keep matching pairs |
| |
| BASIC SYNTAX: |
| SELECT a.col, b.col |
| FROM table_a a |
| JOIN table_b b ON a.id = b.a_id |
| WHERE ... |
| |
| ON vs WHERE: |
| ON -> the join condition (which pairs survive) |
| WHERE -> filter applied after the join |
| (Identical for INNER, very different for OUTER joins) |
| |
| THE SIX FLAVORS: |
| INNER -> only matching pairs |
| LEFT -> all left rows + matches (NULLs for unmatched) |
| RIGHT -> all right rows + matches |
| FULL -> all rows from both sides |
| CROSS -> cartesian product, no condition |
| SELF -> a table joined to itself |
| |
| CARDINALITY: |
| 1:1 -> row counts unchanged |
| 1:N -> left rows multiplied by matches on the right |
| M:N -> requires a junction table |
| |
| WATCH FOR: |
| Missing ON clause -> cartesian explosion |
| Ambiguous columns -> always alias and qualify |
| Wrong join flavor -> rows silently dropped or kept |
| Filter in WHERE on -> turns LEFT JOIN into INNER JOIN |
| LEFT JOIN's right |
| Joining many+many -> aggregates inflated, use CTEs |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. Tables are split for integrity, joins put them back |
| 2. Foreign keys are the arrows that joins follow |
| 3. A join is "cartesian product, then filter" conceptually |
| 4. Always use ON for the join condition, never omit it |
| 5. Alias every table and qualify every column |
| 6. INNER drops unmatched rows; LEFT preserves the left side |
| 7. WHERE filters AFTER the join -- watch out with LEFT JOIN |
| 8. Know the cardinality before you write the join |
| 9. Aggregates over multi-join queries are easy to inflate |
| 10. Count result rows -- "looks right" is not "is right" |
| |
+---------------------------------------------------------------+| Concept | What It Is | Why It Matters |
|---|---|---|
| Normalization | Splitting data so each fact lives once | Eliminates update/insert/delete anomalies |
| Foreign key | Column referencing another table's PK | Enforces integrity; signals join intent |
| Primary key | Unique row identifier | Anchor that foreign keys point at |
| Join condition (ON) | Rule for which row pairs survive | Decides the shape of the result |
| Cartesian product | Every left row x every right row | The conceptual base every join filters |
| INNER JOIN | Keep matching pairs only | Default; drops unmatched rows |
| LEFT JOIN | Keep all left rows + matches | Use when left side must be preserved |
| Cardinality | How many rows match across sides | Determines whether result rows multiply |
| Junction table | Bridge for many-to-many | Cannot M:N join without one |
| Alias | Short name for a table in a query | Required to disambiguate columns |
Prev: Lesson 4.6 -- ORDER BY Next: Lesson 5.2 -- INNER JOIN
This is Lesson 5.1 of the Database Interview Prep Course -- 12 chapters, 58 lessons.