SQL, Joins & Relationships
Lesson 2 of 5
2SQL, Joins & Relationships
The big pictureHow data is split across several tables linked by keys, and how JOIN and subqueries stitch it back together to answer questions no single table can.
What the picture shows: Each row in orders carries a customer number, and the join finds the matching row in customers, giving a result with the order and its owner together.
Map of this part
The concepts in order, click any one to jump to it:
Two ways into the same idea
Instead of writing the customer's address on every invoice, you keep customers in one book and invoices in another, and write only the customer's number on each invoice. A join puts the two books side by side and matches the numbers to answer a question like "how much did each customer spend this month?".
A PRIMARY KEY identifies a row, and a FOREIGN KEY points to a row in another table (in SQLite enable it with PRAGMA foreign_keys = ON). INNER JOIN returns only matches, LEFT JOIN keeps every left row, a subquery puts one SELECT inside another, and joins combine with GROUP BY.
Required Fundamentals
This part builds directly on what came before:
SELECT, WHERE and GROUP BY (the SQL CRUD lesson)
Every join query is an ordinary SELECT with a wider source: instead of one table in FROM there is a table produced by the join. So WHERE, GROUP BY, HAVING and ORDER BY stay the same and operate on the rows the join produces.
Nested loops
Picture a join as two nested loops: for each row of the first table, search the second for rows where the ON condition holds. The engine uses indexes to avoid scanning everything, but the meaning is that of the two loops, which explains why a join with no condition becomes a Cartesian product.
Aggregates and NULL
An outer join’s result contains NULL in the columns of the unmatched table, and the way COUNT(*) versus COUNT(col) treat NULL (the SQL CRUD lesson) is what makes your counts right or wrong here.
Core Concepts
The central idea: never repeat a fact in more than one place. A customer’s name is written once in the customers table and each order merely points to it by number. A join is how we reassemble the fact at read time.
1Keys and relationships
Primary Key
A column (or several) that identifies each row uniquely and never accepts NULL, like customers.id. The table that owns it is the parent table when other tables point at it.
Foreign Key
A column in the child table that holds a primary-key value of the parent: orders.customer_id REFERENCES customers(id). It lets the database guarantee that no order points at a customer who does not exist.
1️⃣ ↔ 1️⃣ One-to-one
A customer has at most one loyalty card. Implemented by making the child table’s primary key itself a foreign key: loyalty_cards.customer_id PRIMARY KEY REFERENCES customers(id).
1️⃣ ↔ ♾️ One-to-many
A customer has many orders and each order belongs to one customer. The simplest and most common relationship: the foreign key sits on the “many” side (orders.customer_id).
↔ ♾️ Many-to-many
An order holds many drinks and a drink sits in many orders. One column cannot represent that, so we add a junction table, order_items, with a foreign key to each side, a composite primary key (order_id, drink_id), and the quantity too.
104 belongs to a guest with no account, so its customer_id is NULL. That is allowed and meaningful: “no customer”. It also explains why an inner join drops this order from the result while an outer join keeps it.
2Referential integrity in SQLite
Declaring REFERENCES alone is not enough in SQLite: foreign-key checking is off by default on every new connection. You turn it on with PRAGMA foreign_keys = ON; at the start of each connection, and it cannot be changed inside a transaction in progress. Once on, the engine refuses to insert an order for a missing customer and refuses to delete a parent that has children, unless a different behavior is set with ON DELETE.
ON DELETE action | What happens to the child rows when the parent is deleted |
|---|---|
NO ACTION (default) | Rejects the delete/update if child rows still point at it. |
RESTRICT | Like the previous one but it refuses immediately, before any other change. |
SET NULL | Puts NULL in the child rows’ key (the column must allow NULL). |
SET DEFAULT | Puts the column’s default value in the child rows. |
CASCADE | Automatically deletes the child rows (or updates their key) when the parent changes. |
PRAGMA foreign_keys = ON: you declare complete relationships, then discover that deletes and inserts pass without any objection and your table holds “orphan” rows pointing at a missing parent. Without that statement, foreign keys are merely documentation.
3Join types
| Type | What it returns | Café example |
|---|---|---|
INNER JOIN | Only rows that have a match in both tables. | Orders that have a registered customer |
LEFT JOIN | Every row of the left table, with NULLs where there is no match. | All customers, even those who never ordered |
CROSS JOIN | Every possible combination (the Cartesian product): m × n rows. | Every customer with every drink |
RIGHT JOIN / FULL JOIN | Right side / both sides, available in SQLite only since 3.39.0. | Alternative: swap the tables and use LEFT JOIN |
RIGHT JOIN and FULL JOIN in version 3.39.0 (June 2022). Older versions reject them with an error, and your environment may be one of them. The safe habit: write everything with INNER and LEFT only; a right join is a left join after swapping the two tables, and a full join is a LEFT JOIN plus the unmatched rows from the other side added with UNION ALL.
4Details of writing a join
Aliases
FROM orders o JOIN customers c shortens the code and is mandatory when joining a table to itself. When two columns share a name (id in both tables) you must say which one you mean, c.id or o.id, or you get an ambiguous column name error.
Self join
Joining a table to a copy of itself under two aliases, to compare its rows with each other: pairs of customers in the same city a.city = b.city AND a.id < b.id (the second condition prevents duplicate pairs and a row matching itself).
USING and NATURAL
JOIN … USING (col) is a shortcut when the column has the same name in both tables. NATURAL JOIN joins on every same-named column automatically, so its result changes if you add a column later; better to avoid it and write an explicit ON.
LEFT JOIN, the ON condition decides which rows match, while WHERE runs after the join and removes rows from the output. Putting a condition on the right table in WHERE removes the rows that hold NULL there, so the outer join silently behaves like an inner one. Rule: match-defining conditions go in ON, final output filters go in WHERE.
FROM orders, customers with no condition (or a JOIN with no ON) produces every possible combination: 5 orders × 4 customers = 20 rows, most of them false. The warning sign: far more rows than you expected.
5Subqueries
A subquery is a SELECT inside a SELECT, in parentheses. Often the same question can be written with a JOIN or with a subquery; choose the one that reads most clearly.
| Form | Meaning | Example |
|---|---|---|
| Scalar | A query returning one row and one column, used as if it were a value. | WHERE price_halalas > (SELECT AVG(price_halalas) FROM drinks) |
IN (…) | Returns a list of values; true if the value is in it. | WHERE id IN (SELECT customer_id FROM orders) |
EXISTS (…) | True if the query returns at least one row; its contents do not matter. | WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) |
| Correlated | References a column of the outer query, so it is recomputed for each outer row. | … > (SELECT AVG(price_halalas) FROM drinks WHERE category = d.category) |
| Derived table | A subquery in FROM given an alias and treated as a table. | FROM (SELECT … GROUP BY …) AS t |
NOT IN (…) list contains a single NULL, the condition holds for no row at all and you get an empty result with no error. That is exactly what happens with orders.customer_id because it contains the guest order. The safer fixes: NOT EXISTS, or an outer join with IS NULL.
6Joins with aggregation
After a join the rows get “wider” and may multiply: joining an order to its items gives a row per item, so counting orders after that join counts one order more than once. Use COUNT(DISTINCT o.id). And after a LEFT JOIN, COUNT(*) counts an unmatched row as one, whereas COUNT(o.id) gives zero because that column is NULL, so use the latter to count children.
How Does a Join Actually Work?
The map of our café
Diagram 23, an entity-relationship (ER) diagram of the café: each line links parent to child, and the symbol at its end shows the count (zero or one, exactly one, zero or more).
Which join do I choose?
Diagram 24, a decision tree for choosing the join type, and how to reach the “orphan” or unmatched rows.
What the engine does with each row
Diagram 25, the join logically comes before WHERE; that is why a WHERE condition can erase the NULL rows a LEFT JOIN produced.
Worked Example: A Café with Five Tables
We build the whole café database and then ask it questions that need more than one table. The tables: customers, drinks, orders, order_items (the junction) and loyalty_cards (one-to-one).
Step 1, the schema
PRAGMA foreign_keys = ON; -- per connection!
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL
);
CREATE TABLE drinks (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
category TEXT NOT NULL CHECK (category IN ('hot', 'cold')),
price_halalas INTEGER NOT NULL CHECK (price_halalas > 0)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id), -- NULL = guest
order_date TEXT NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id),
drink_id INTEGER NOT NULL REFERENCES drinks(id),
qty INTEGER NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, drink_id)
);
CREATE TABLE loyalty_cards (
customer_id INTEGER PRIMARY KEY REFERENCES customers(id),
points INTEGER NOT NULL DEFAULT 0 CHECK (points >= 0)
);
The data: four customers (Noura and Lina in Riyadh, Sultan in Jeddah, Faisal in Dammam), five drinks, five orders (one, 104, for a guest with no account), and two loyalty cards, for Noura and Lina only.
Step 2, the inner join
SELECT o.id AS order_id, c.name, o.order_date FROM orders o INNER JOIN customers c ON c.id = o.customer_id ORDER BY o.id;
| order_id | name | order_date |
|---|---|---|
| 101 | Noura | 2026-10-01 |
| 102 | Noura | 2026-10-03 |
| 103 | Sultan | 2026-10-03 |
| 105 | Lina | 2026-10-05 |
Only four of the five orders: the guest order 104 vanished because it has no counterpart in the customers table.
Step 3, the outer join and unmatched rows
SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id ORDER BY c.id, o.id;
| name | order_id |
|---|---|
| Noura | 101 |
| Noura | 102 |
| Sultan | 103 |
| Lina | 105 |
| Faisal | NULL |
Faisal appears with NULL because he has ordered nothing. To isolate only such customers we add WHERE o.id IS NULL (this pattern is called an anti-join):
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
| name |
|---|
| Faisal |
Step 4, ON versus WHERE
We want every customer with their orders on 3 October if any. Condition in ON:
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date = '2026-10-03'
ORDER BY c.id;
| name | order_id |
|---|---|
| Noura | 102 |
| Sultan | 103 |
| Lina | NULL |
| Faisal | NULL |
Move the same condition to WHERE:
SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.order_date = '2026-10-03' ORDER BY c.id;
| name | order_id |
|---|---|
| Noura | 102 |
| Sultan | 103 |
Lina and Faisal disappeared because WHERE removed the NULL rows: the outer join turned into an inner one in practice.
Step 5, joining three or more tables through the junction
SELECT o.id AS order_id,
COALESCE(c.name, '(guest)') AS customer,
d.name AS drink,
oi.qty,
oi.qty * d.price_halalas AS line_total
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN drinks d ON d.id = oi.drink_id
ORDER BY o.id, d.id;
| order_id | customer | drink | qty | line_total |
|---|---|---|---|---|
| 101 | Noura | Flat White | 2 | 3200 |
| 101 | Noura | Cortado | 1 | 1400 |
| 102 | Noura | Cold Brew | 1 | 1800 |
| 103 | Sultan | Spanish Latte | 2 | 3900 |
| 104 | (guest) | Flat White | 1 | 1600 |
| 105 | Lina | Cold Brew | 3 | 5400 |
| 105 | Lina | Spanish Latte | 1 | 1950 |
Notice that the LEFT JOIN on customers kept the guest order (104), while joining items and drinks with a plain JOIN is safe because every item always has an order and a drink. You read joins left to right: each JOIN adds a table to the previous result.
Step 6, joins with aggregation
SELECT c.name,
COUNT(DISTINCT o.id) AS orders,
COALESCE(SUM(oi.qty * d.price_halalas), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN drinks d ON d.id = oi.drink_id
GROUP BY c.id
ORDER BY spent DESC, c.id;
| name | orders | spent |
|---|---|---|
| Lina | 1 | 7350 |
| Noura | 2 | 6400 |
| Sultan | 1 | 3900 |
| Faisal | 0 | 0 |
COUNT(DISTINCT o.id) stops an order being counted twice when it holds two items (Noura’s order 101 has two rows after the join). Without COALESCE, Faisal’s total would be NULL rather than 0.
SELECT c.name,
COUNT(*) AS count_star,
COUNT(o.id) AS count_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;
| name | count_star | count_orders |
|---|---|---|
| Noura | 2 | 2 |
| Sultan | 1 | 1 |
| Lina | 1 | 1 |
| Faisal | 1 | 0 |
For Faisal COUNT(*) gives 1 (the unmatched row) and COUNT(o.id) gives 0, which is the correct number of orders.
Step 7, one-to-one, and a self join
SELECT c.name, COALESCE(l.points, 0) AS points FROM customers c LEFT JOIN loyalty_cards l ON l.customer_id = c.id ORDER BY c.id;
| name | points |
|---|---|
| Noura | 120 |
| Sultan | 0 |
| Lina | 40 |
| Faisal | 0 |
SELECT a.name AS first, b.name AS second, a.city FROM customers a JOIN customers b ON a.city = b.city AND a.id < b.id;
| first | second | city |
|---|---|---|
| Noura | Lina | Riyadh |
Step 8, the Cartesian-product mistake
SELECT COUNT(*) AS rows_returned FROM customers CROSS JOIN drinks;
| rows_returned |
|---|
| 20 |
4 customers × 5 drinks = 20 rows of meaningless combinations. That is what you get when you forget ON.
Step 9, subqueries
IN: customers who have ever ordered a cold drink.
SELECT name
FROM customers
WHERE id IN (SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN drinks d ON d.id = oi.drink_id
WHERE d.category = 'cold')
ORDER BY id;
| name |
|---|
| Noura |
| Sultan |
| Lina |
A correlated query: drinks pricier than the average of their own category (hot averages 1500, cold 1950).
SELECT d.name, d.category, d.price_halalas
FROM drinks d
WHERE d.price_halalas > (SELECT AVG(price_halalas)
FROM drinks
WHERE category = d.category)
ORDER BY d.id;
| name | category | price_halalas |
|---|---|---|
| Flat White | hot | 1600 |
| Matcha | cold | 2100 |
And here is the NOT IN trap. The intent: customers who have ordered nothing.
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
| name |
|---|
Empty! Because orders.customer_id contains NULL (the guest). Whereas:
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
| name |
|---|
| Faisal |
When the column is NOT NULL, as with order_items.drink_id, NOT IN is safe:
SELECT name FROM drinks WHERE id NOT IN (SELECT drink_id FROM order_items);
| name |
|---|
| Matcha |
Step 10, standing in for RIGHT and FULL
“Every order with the customer’s name if there is one” is a right join on customers; we write it by swapping the two tables:
SELECT o.id AS order_id, c.name FROM orders o LEFT JOIN customers c ON c.id = o.customer_id ORDER BY o.id;
| order_id | name |
|---|---|
| 101 | Noura |
| 102 | Noura |
| 103 | Sultan |
| 104 | NULL |
| 105 | Lina |
And “all customers and all orders” is a full join, built from a LEFT JOIN plus the orphan orders:
SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id UNION ALL SELECT NULL, o.id FROM orders o WHERE o.customer_id IS NULL;
| name | order_id |
|---|---|
| Noura | 101 |
| Noura | 102 |
| Sultan | 103 |
| Lina | 105 |
| Faisal | NULL |
| NULL | 104 |
Step 11, foreign-key protection
| Attempt (after enabling the PRAGMA) | Result |
|---|---|
INSERT INTO orders VALUES (106, 99, '2026-10-06') | FOREIGN KEY constraint failed |
DELETE FROM customers WHERE id = 1 | FOREIGN KEY constraint failed |
The first because customer 99 does not exist, the second because Noura has child orders and the default NO ACTION refuses.
Step 12, from Python
import sqlite3
conn = sqlite3.connect("cafe.db")
conn.execute("PRAGMA foreign_keys = ON") # every new connection
sql = """
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = ?
GROUP BY c.id
"""
for name, n in conn.execute(sql, ("Riyadh",)):
print(name, n)
conn.close()
PRAGMA runs on every new connection, and the value is passed with the ? placeholder, not by splicing text.
Myth vs. Reality
| The myth | The reality |
|---|---|
JOIN alone means both an inner and an outer join | A bare JOIN is an INNER JOIN. An outer join must be written explicitly: LEFT JOIN. |
Declaring a FOREIGN KEY automatically protects my data in SQLite | Checking is off by default; you need PRAGMA foreign_keys = ON on every connection. |
A condition in WHERE and one in ON are always equivalent | Equivalent only in an inner join. In a LEFT JOIN they differ because WHERE removes NULL rows after the join. |
NOT IN is always the simplest way to find “those without” | It fails silently (an empty result) if the list holds a NULL. NOT EXISTS or an anti-join is safer. |
A subquery is always slower than a JOIN | Not a rule; the optimizer rewrites many of them and performance depends on indexes and data. Pick the clearer one, then measure if needed. |
Where Are These Tools Actually Used?
Invoices and reports
Every invoice pulls the order, customer, items and prices from four tables with a chain of JOINs.
Customer analysis
“Who has not bought in a month?” is an anti-join; “top-spending customers” is a join with SUM and GROUP BY.
Data integrity
Foreign keys and ON DELETE actions prevent orphan orders and decide what happens when a customer is deleted.
ORM tools
Object-relational mappers (the next step on the path) translate relationships between classes into foreign keys and joins, so understanding what is underneath makes them transparent.
Try it yourself
You want every customer, including those who never ordered. Which join?
LEFT JOIN keeps every row of the left table, here customers, and puts NULL where there is no order.
Quick Review Flashcards
LEFT JOIN … WHERE right.col = x return fewer rows than expected?WHERE removes the NULL rows the join produced, turning it into an inner join. Move the condition to ON.m × n (the Cartesian product).PRAGMA foreign_keys = ON; on every new connection.NOT EXISTS or LEFT JOIN … WHERE o.id IS NULL.Check Your Understanding
1. (Recall) What is the difference between INNER JOIN and LEFT JOIN for the guest order (104)?
The inner join drops the order because it has no matching customer. A LEFT JOIN from orders to customers keeps it with NULL in the customer columns.
2. (Why) Why give each table an alias even when we are not joining it to itself?
Because same-named columns (id, say) need their source stated or you get ambiguous column name, and for brevity and readability.
3. (Spot the error) SELECT c.name, COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id; says Faisal has one order. What is wrong?
COUNT(*) counts the unmatched row the outer join produced. The correct form is COUNT(o.id), which gives 0.
4. (Spot the error) Why does SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) return zero rows although Faisal ordered nothing? How do you fix it?
Because the IN list contains NULL (the guest order), so NOT IN holds for no row. Fix it with NOT EXISTS or with WHERE customer_id IS NOT NULL inside the subquery.
5. (Predict) How many rows does SELECT * FROM orders CROSS JOIN drinks return?
25 = 5 orders × 5 drinks.
Resources for this part
Sorted by kind: start with the official docs when you want the precise reference, or with an article or video when you want the explanation first.
Resources for this lesson
Grouped by type. Start with the official docs for precision, or an article or video for the explanation.
Official docs1
Done with this lesson?
Sign in to save your progress.