Skip to content

Intermediate · SQL in depth · topic 21 of 54 · 20 min

Joins: combining tables

Explanation

Real databases keep customers, orders and products in separate tables and link them with ids. A JOIN puts matching rows from two tables side by side in one result. INNER JOIN keeps only rows that match on both sides; LEFT JOIN keeps every row from the left table, even with no match, so you can find customers who never ordered.

Why data lives in many tables

Imagine writing the customer's name and city on every order row. If Priya places 50 orders, her city is written 50 times. When she moves, you must change 50 rows, and if you miss one the data disagrees with itself. So we store each customer once in a customers table, give the row an id, and let each order store only customer_id.

A column like orders.customer_id that points to a row in another table is a foreign key. The column it points to, customers.id, is the primary key of that table: a value that is unique for every row. With REFERENCES the database refuses an order for a customer who does not exist.

sql
CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date  TEXT NOT NULL,
  amount      INTEGER NOT NULL,
  status      TEXT NOT NULL
);
Each order points to its customer through customer_id (a foreign key).
Think of it like this: Think of a school register and a marks sheet. The register lists each student once with a roll number. The marks sheet only writes roll numbers next to marks. To print a report card you look up each roll number in the register. A JOIN is that lookup, done by the database for thousands of rows at once.

INNER JOIN: only matching rows

INNER JOIN (or just JOIN) pairs each row of the first table with the rows of the second table where the ON condition is true. Rows with no partner on the other side are dropped. The short names after the table (orders o, customers c) are aliases: they save typing and make it clear which table a column comes from.

sql
SELECT o.id, c.name, o.amount
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
WHERE o.id <= 104
ORDER BY o.id;
For every order, look up the customer whose id equals the order's customer_id.
o.idc.nameo.amount
101Aarav Sharma1270
102Priya Nair320
103Aarav Sharma380
104Rohan Gupta2249

LEFT JOIN: keep everything on the left

LEFT JOIN keeps every row of the left (first) table. When there is no matching row on the right, the right-side columns are filled with NULL, which means "no value". That makes LEFT JOIN plus WHERE ... IS NULL the classic way to find things that are missing: customers with no orders, products never sold, students who skipped an exam.

sql
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id BETWEEN 4 AND 6
ORDER BY c.id;
Imran and Kavya have no orders, so their order_id is NULL.
c.nameorder_id
Sneha Iyer105
Imran KhanNULL
Kavya ReddyNULL

Three tables and more

sql
SELECT o.id, p.name, oi.qty, oi.qty * p.price AS line_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.id = 104;
Each JOIN adds one more table: orders, then the items in them, then the product of each item.
Tip: Build big joins one table at a time. Run the query after each new JOIN and check that the row count makes sense before adding the next one.
Careful: A missing or wrong ON condition pairs every row with every row (a cross join). 1,000 customers and 10,000 orders become 1 crore rows. If a result suddenly has far too many rows, check your ON.
Careful: When two tables share a column name (both have id), write o.id or c.id. A bare id gives the error "ambiguous column name". Also: a WHERE filter on the right table of a LEFT JOIN (WHERE o.status = 'delivered') removes the NULL rows and quietly turns it back into an INNER JOIN. Put such filters in the ON clause instead.
JoinKeeps
INNER JOINOnly rows that match on both sides
LEFT JOINAll rows of the left table, NULLs where the right has no match
RIGHT JOINAll rows of the right table (rarely used; swap the tables and use LEFT)
FULL OUTER JOINAll rows of both tables (PostgreSQL, and SQLite 3.39 or newer)

Watch the lesson

Joins: combining tables · 1/7

Joins: combining tables

Real apps never keep everything in one table. Today we learn how to put tables back together with joins.

Prefer a video by a teacher? Find free videos on “sql joins explained inner join left join tutorial” on YouTube ↗

In real life: The "My orders" page of a shopping app

When you open "My orders" in a shopping app, the backend runs one query that joins orders with order_items and products, filtered by your customer id. You see each order with product names and pictures, although the orders table itself only holds ids and amounts. The admin dashboard uses a LEFT JOIN the other way round: "customers who signed up but never ordered" is the list the marketing team uses to send a discount coupon.

sql
SELECT o.id, o.order_date, p.name, oi.qty
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 2
ORDER BY o.order_date DESC;
The query behind "My orders" for customer 2.

Practice (0/3 done)

Task 1 · Code · SQL

Tables: customers(id, name, city) and orders(id, customer_id, order_date, amount, status). Show the order id, the customer's name and the amount for every order whose status is 'delivered', highest amount first.

Task 2 · Code · SQL

Find the customers who have never placed an order. Show only their names, in alphabetical order. Use a LEFT JOIN from customers to orders.

Task 3 · Code · SQL

Tables: orders(id, order_date, ...), order_items(order_id, product_id, qty) and products(id, name, category, price). For orders placed on or after 2026-03-10, list the order id, the product name and the qty. Sort by order id, then product name.

Quick quiz

Question 1

1.Why is a customer's city stored in a customers table instead of on every order row?

Question 2

2.Customers has 7 rows; 2 of them have no orders. What does an INNER JOIN of customers with orders do with those 2 customers?

Question 3

3.Which query finds products that were never sold?

Question 4

4.You join orders and customers and get the error "ambiguous column name: id". What is the fix?

Question 5

5.A query joining 1,000 customers and 10,000 orders returns 1,00,00,000 rows. What most likely went wrong?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: GROUP BY and HAVING →

← Previous: Counting and totals: aggregate functions