Why data is split across tables, and joins
30 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A join builds one result from two tables by pairing each row of one table with the matching rows of the other. Databases keep customers, orders and products in separate tables on purpose, so almost every real report needs a join to put them back together.
One fact in one place
A well-designed database stores each fact exactly once. Ravi's city is one fact, so it lives in one row of the customers table, not in every order he places.
Example 1 · One fact in many rows goes out of sync
DROP TABLE IF EXISTS order_sheet;CREATE TABLE order_sheet ( order_id INT PRIMARY KEY, customer_name VARCHAR(60), customer_city VARCHAR(40), product VARCHAR(80), amount DECIMAL(10,2));
INSERT INTO order_sheet VALUES (1, 'Ravi Kumar', 'Chennai', 'Clean Code', 499.00), (2, 'Ravi Kumar', 'Chennai', 'Rubik''s Cube', 299.00), (3, 'Asha Rao', 'Bengaluru', 'Redmi Note 13', 17999.00), (4, 'Ravi Kumar', 'Chennai', 'boAt Airdopes 141', 1299.00);
-- Ravi moves to Coimbatore, but only his latest order gets the changeUPDATE order_sheet SET customer_city = 'Coimbatore' WHERE order_id = 4;
SELECT order_id, customer_name, customer_city, productFROM order_sheetORDER BY order_id;+----------+---------------+---------------+-------------------+ | order_id | customer_name | customer_city | product | +----------+---------------+---------------+-------------------+ | 1 | Ravi Kumar | Chennai | Clean Code | | 2 | Ravi Kumar | Chennai | Rubik's Cube | | 3 | Asha Rao | Bengaluru | Redmi Note 13 | | 4 | Ravi Kumar | Coimbatore | boAt Airdopes 141 | +----------+---------------+---------------+-------------------+
What this code does
- Ravi's city is stored three times. The
UPDATEchanged one copy, so the table now claims he lives in Chennai and in Coimbatore at once. - In ShopLite the city is stored once, in
customers, so oneUPDATEon one row fixes it for every order. Joins are how the orders get that city back.
The pain without this rule is easy to see. If an order sheet repeats the customer's name and city on every row, a change of city must be made on every one of those rows. Miss one row and the table now says two different things about the same person. Repeated data also wastes space and makes typing mistakes (Chenai) likely.
So ShopLite keeps customers in customers, orders in orders and products in products. Each table describes one kind of thing. The price of this design is that a question such as "which city did order 7 go to?" now needs data from two tables.
Keys connect the tables
A foreign key is a column that stores the primary key value of a row in another table, so it links the two tables. orders.customer_id holds the customer_id of the customer who placed the order.
An order must say which customer placed it, without copying the customer's details into every order. A column holding the customer's id does exactly that.
Example 2 · Following the key by eye
SELECT customer_id, name, cityFROM customersWHERE customer_id IN (1, 2, 3)ORDER BY customer_id;
SELECT order_id, customer_id, order_date, statusFROM ordersWHERE customer_id IN (1, 2, 3)ORDER BY order_id;+-------------+------------+-----------+ | customer_id | name | city | +-------------+------------+-----------+ | 1 | Asha Rao | Bengaluru | | 2 | Ravi Kumar | Chennai | | 3 | Meena Iyer | Chennai | +-------------+------------+-----------+ +----------+-------------+------------+-----------+ | order_id | customer_id | order_date | status | +----------+-------------+------------+-----------+ | 1 | 1 | 2025-01-05 | DELIVERED | | 2 | 2 | 2025-01-18 | DELIVERED | | 3 | 1 | 2025-02-02 | DELIVERED | | 4 | 3 | 2025-02-10 | CANCELLED | | 7 | 2 | 2025-03-15 | SHIPPED | | 10 | 1 | 2025-04-12 | SHIPPED | | 15 | 2 | 2025-06-01 | PLACED | +----------+-------------+------------+-----------+
What this code does
- The first table is the parent: one row per customer. The second is the child:
customer_idin each order points back to a row in the first table. - Customer 1 (Asha Rao) appears in orders 1, 3 and 10, and customer 2 (Ravi Kumar) in orders 2, 7 and 15. Matching these ids by hand is exactly what a join automates.
Tables are connected by keys. The primary key of customers is customer_id: a value that is unique for every customer.
The foreign key is the link. Order 3 does not store "Asha Rao, Bengaluru"; it stores customer_id = 1, and row 1 of customers says who that is. Ids are used instead of names because names are not unique (two customers can both be called Rahul) and names can change, while an id never does.
A join pairs rows on a condition
A join is a query that combines rows of two tables into one result, pairing the rows whose key values satisfy the condition written after ON.
To show each order next to its customer's name, the two tables must be read together. Pair every order with the customer whose id it stores.
Example 3 · Your first join: orders with customer names
SELECT orders.order_id, orders.order_date, customers.name, customers.cityFROM ordersJOIN customers ON orders.customer_id = customers.customer_idWHERE orders.customer_id IN (1, 2, 3)ORDER BY orders.order_id;+----------+------------+------------+-----------+ | order_id | order_date | name | city | +----------+------------+------------+-----------+ | 1 | 2025-01-05 | Asha Rao | Bengaluru | | 2 | 2025-01-18 | Ravi Kumar | Chennai | | 3 | 2025-02-02 | Asha Rao | Bengaluru | | 4 | 2025-02-10 | Meena Iyer | Chennai | | 7 | 2025-03-15 | Ravi Kumar | Chennai | | 10 | 2025-04-12 | Asha Rao | Bengaluru | | 15 | 2025-06-01 | Ravi Kumar | Chennai | +----------+------------+------------+-----------+
What this code does
- Each order row found the one customer row with the same
customer_id, and the two were glued into one result row. Seven orders in, seven rows out. orders.order_idandcustomers.namesay which table each column comes from. TheWHEREfilter runs on the joined rows, just as on a single table.
JOIN ... ON pairs rows from two tables wherever the condition after ON is true. FROM orders JOIN customers ON orders.customer_id = customers.customer_id pairs each order with the customer whose id it stores.
Think of it in three steps: take a row of orders; find every row of customers that makes the condition true; glue each such pair into one wide result row. The result has the columns of both tables, and you pick the ones you want in SELECT. Columns are written as table.column so it is clear which table each one comes from. A join only reads. It does not change orders or customers; it builds a new result each time the query runs. Every order has exactly one customer here, so the join returns exactly one row per order.
Order of execution
Put the join query in order
Drag the lines into the order MySQL expects.
Joining without a condition: every pair
A Cartesian product pairs every row of one table with every row of another table. With no join condition, 2 teams and 3 players give 2 x 3 = 6 pairs.
What happens if the pairing rule is forgotten? Every row of one table meets every row of the other.
Example 4 · Every pair first, then the condition
DROP TABLE IF EXISTS ipl_teams, ipl_players;CREATE TABLE ipl_teams (team_id INT PRIMARY KEY, team VARCHAR(30));CREATE TABLE ipl_players (player VARCHAR(40), team_id INT);
INSERT INTO ipl_teams VALUES (1, 'Chennai'), (2, 'Mumbai');INSERT INTO ipl_players VALUES ('Dhoni', 1), ('Rohit', 2), ('Jadeja', 1);
-- No condition: every player paired with every teamSELECT *FROM ipl_players, ipl_teamsORDER BY ipl_players.player, ipl_teams.team_id;
-- With the condition: only the pairs where the ids agreeSELECT ipl_players.player, ipl_teams.teamFROM ipl_playersJOIN ipl_teams ON ipl_players.team_id = ipl_teams.team_idORDER BY ipl_players.player;+--------+---------+---------+---------+ | player | team_id | team_id | team | +--------+---------+---------+---------+ | Dhoni | 1 | 1 | Chennai | | Dhoni | 1 | 2 | Mumbai | | Jadeja | 1 | 1 | Chennai | | Jadeja | 1 | 2 | Mumbai | | Rohit | 2 | 1 | Chennai | | Rohit | 2 | 2 | Mumbai | +--------+---------+---------+---------+ +--------+---------+ | player | team | +--------+---------+ | Dhoni | Chennai | | Jadeja | Chennai | | Rohit | Mumbai | +--------+---------+
What this code does
- The first result has 3 × 2 = 6 rows. Rows such as Dhoni with team 2 (Mumbai) are pairs, not facts: the two
team_idcolumns disagree. - The join condition keeps only the 3 rows where both
team_idvalues are equal. That is all a join is: every pair, filtered by the condition.
If you list two tables and give no condition, SQL pairs every row of the first table with every row of the second. This is called the Cartesian product: 2 teams and 3 players give 2 × 3 = 6 pairs, most of them meaningless.
The Cartesian product is the honest model of what a join does. Start from every pair, then keep only the pairs where the condition is true. The condition is what turns 6 meaningless pairs into 3 correct ones. The danger is forgetting the condition by mistake. With 10 customers and 15 orders, you get 150 rows instead of 15, and every total built on them is wrong. MySQL gives no error for this: the query is valid SQL, just the wrong question.
Predict the output
Predict the row count
How many rows does each query return?
Round 1 of 2
Every order has exactly one customer (15 orders, 10 customers).
FROM a, b WHERE ...) is the old way to write a join. It works, but JOIN ... ON keeps the pairing condition next to the table and is harder to forget.Next step
Check your understanding
12 quick questions