Joins and Relationships

In this lesson, you'll learn one of SQL's most powerful features: joining data from multiple tables. Real-world databases rarely store all information in a single table, so understanding joins is essential.

What You'll Learn

By the end of this lesson you will write one query that pulls a customer, their order and that order's line items into a single result — and, before you run it, you will be able to say how many rows it returns and why that many. That second half is the whole lesson. A join does not return one row per customer; it returns one row per matching combination, and a total computed over those rows is inflated by exactly the number of matches. Getting this wrong is how a report doubles its own revenue figure while every individual number in it looks right.

This is the joined-line-items milestone of the warehouse project, and the capstone depends on it: the order detail view there is a four-way join, and its totals are correct only if you can predict the row count going in. You arrive able to load and correct rows and to check a write by its affected-row count (Inserting and Updating Data); what is new is that a query now reads two or more tables at once, and the row count stops being a property of any one of them.

Why Use Multiple Tables?

Instead of duplicating data, we split it into related tables. This approach:

• Reduces data redundancy
• Makes updates easier
• Keeps data consistent
• Improves organization

For example, instead of storing customer details with every order, we store customers once and reference them from orders.

Table Relationships

One-to-Many Relationship

The most common relationship type. One record in Table A relates to many records in Table B.

Example: One customer can have many orders.

-- Create customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

-- Create orders table. The customer_id column holds the id of a row
-- in customers: that pointer is what makes the two tables related.
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product TEXT,
    amount_cents INTEGER,
    order_date TEXT
);

-- Insert customers
INSERT INTO customers (id, name, email)
VALUES
    (1, 'Alice Johnson', 'alice@example.com'),
    (2, 'Bob Smith', 'bob@example.com'),
    (3, 'Charlie Davis', 'charlie@example.com');

-- Insert orders
INSERT INTO orders (id, customer_id, product, amount_cents, order_date)
VALUES
    (1, 1, 'Laptop', 99999, '2025-01-10'),
    (2, 1, 'Mouse', 2499, '2025-01-11'),
    (3, 2, 'Keyboard', 7999, '2025-01-12'),
    (4, 3, 'Monitor', 29999, '2025-01-13');

-- View the separate tables
SELECT id, name, email FROM customers ORDER BY id;
SELECT id, customer_id, product, amount_cents FROM orders ORDER BY id;

The customer_id column is a foreign key in the informal sense: a column whose values are meant to be ids from another table. Nothing in the schema above says so, though, which means nothing is checking it — an order for customer 99 would be accepted without complaint even though no such customer exists.

You can ask the database to enforce the rule, by declaring the column with a REFERENCES clause and switching enforcement on. In this environment enforcement starts switched off on every single Run, so the pragma is not optional decoration:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), product TEXT);

INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Bob Smith');
INSERT INTO orders VALUES (1, 1, 'Laptop');
INSERT INTO orders VALUES (2, 99, 'Ghost order');

The first order points at customer 1 and goes in. The second names customer 99, who does not exist, and the database refuses to write it:

Error: FOREIGN KEY constraint failed

Remove the PRAGMA foreign_keys = ON; line and run it again: the same INSERT succeeds, silently, and the orders table now holds a row pointing at nobody. The declaration alone is documentation; the pragma is what makes it a rule. The full treatment of constraints and referential integrity comes much later in the track — for now, the point is that a relationship between two tables is a convention your queries rely on, and whether anything enforces it is a separate question you have to ask.

INNER JOIN

INNER JOIN returns rows where there's a match in both tables.

Every Run starts from a fresh, empty database, so this fence rebuilds the two tables rather than reusing the ones above — and it keeps only the columns this example needs, which is why the shapes differ slightly from the previous fence. A script that reads a table always creates that table first, in the same script.

-- Rebuild the two tables: this Run starts from an empty database
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product TEXT,
    amount_cents INTEGER,
    order_date TEXT
);

INSERT INTO customers (id, name, email)
VALUES
    (1, 'Alice Johnson', 'alice@example.com'),
    (2, 'Bob Smith', 'bob@example.com'),
    (3, 'Charlie Davis', 'charlie@example.com');

INSERT INTO orders (id, customer_id, product, amount_cents, order_date)
VALUES
    (1, 1, 'Laptop', 99999, '2025-01-10'),
    (2, 1, 'Mouse', 2499, '2025-01-11'),
    (3, 2, 'Keyboard', 7999, '2025-01-12'),
    (4, 3, 'Monitor', 29999, '2025-01-13');

-- Join customers with their orders
SELECT
    customers.name,
    orders.product,
    orders.amount_cents,
    orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
ORDER BY orders.id;

The model: one row per matching pair

Here is the smallest model that lets you predict a join's result, and it is worth more than any amount of syntax. A join does not attach columns to the rows of the left table. It considers every pairing of a left row with a right row, keeps the pairs for which the ON condition is true, and emits one result row per surviving pair.

Everything else follows from that sentence. Three customers and four orders do not give three rows — they give four, one per matching pair, because each order matches exactly one customer. Alice has two orders, so Alice appears twice, and her name and email are repeated on both rows. A customer with no orders produces no pairs at all and vanishes from the result entirely.

So the row count of a join is not the row count of either table. It is the number of matches, and to predict it you count matches rather than rows.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bo');
INSERT INTO orders VALUES (10,1,1200),(11,1,800),(12,2,400),(13,1,250);

SELECT c.name, o.total_cents
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

Modify it twice, and predict before each Run. First, add WHERE customers.name = 'Alice Johnson' to the fence above and predict the row count — two, because Alice is in two matching pairs, not one. Then change INNER JOIN to LEFT JOIN and predict whether anything changes: it does not, because every order here already matches a customer, and that is exactly the case where the two join types are indistinguishable.

LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for the right table.

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

-- Create orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product TEXT,
    amount_cents INTEGER
);

-- Insert customers
INSERT INTO customers (id, name, city)
VALUES
    (1, 'Alice Johnson', 'New York'),
    (2, 'Bob Smith', 'London'),
    (3, 'Charlie Davis', 'Paris'),
    (4, 'Diana Wilson', 'Tokyo');

-- Insert orders (note: Diana has no orders)
INSERT INTO orders (id, customer_id, product, amount_cents)
VALUES
    (1, 1, 'Laptop', 99999),
    (2, 1, 'Mouse', 2499),
    (3, 2, 'Keyboard', 7999),
    (4, 3, 'Monitor', 29999);

-- Show all customers, even those without orders
SELECT
    customers.name,
    customers.city,
    orders.product,
    orders.amount_cents
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
ORDER BY customers.id, orders.id;

Read Diana's row carefully, because it is the whole point of a LEFT JOIN. She matched nothing, so there was no pair to emit — and rather than dropping her, the join manufactures one row for her with every column from the right table set to NULL, printed here as None. Swap LEFT JOIN for INNER JOIN and that manufactured row disappears along with Diana.

That manufactured row is where LEFT JOIN starts to bite. It is a real row of the result: it is counted, it is sorted, and it reaches any function you apply to the result. A LEFT JOIN keeps every left row, but what it gives you for an unmatched one is a row full of nothing, not an absence of a row.

Modify it twice, and predict before each Run. First, change the join to INNER JOIN and predict the row count — four instead of five, because Diana's manufactured row is the one that goes. Then restore the LEFT JOIN and add WHERE orders.product IS NULL: predict who comes back, and note that this filter on the right table's column is the standard way to ask "which left rows matched nothing?"

Table Aliases

Make queries shorter and more readable using aliases:

-- Create departments table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT
);

-- Create employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary INTEGER
);

-- Insert departments
INSERT INTO departments (id, name, location)
VALUES
    (1, 'Engineering', 'Building A'),
    (2, 'Sales', 'Building B'),
    (3, 'Marketing', 'Building C');

-- Insert employees
INSERT INTO employees (id, name, department_id, salary)
VALUES
    (1, 'John Doe', 1, 75000),
    (2, 'Jane Smith', 2, 65000),
    (3, 'Mike Johnson', 1, 80000),
    (4, 'Sarah Williams', 3, 70000);

-- Use aliases 'e' for employees and 'd' for departments
SELECT
    e.name AS employee_name,
    e.salary,
    d.name AS department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY e.id;

Note that FROM employees e introduces the alias and every later clause uses it, including the ON condition. An alias is available to the whole query once its table has been named, which is why a query can say e.name in the SELECT list that appears above the FROM.

Joining Multiple Tables

You can join more than two tables in a single query:

-- Create authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

-- Create books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    publisher_id INTEGER
);

-- Create publishers table
CREATE TABLE publishers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

-- Insert data
INSERT INTO authors (id, name, country)
VALUES
    (1, 'J.K. Rowling', 'UK'),
    (2, 'George Orwell', 'UK'),
    (3, 'Ernest Hemingway', 'USA');

INSERT INTO publishers (id, name, city)
VALUES
    (1, 'Penguin Books', 'London'),
    (2, 'HarperCollins', 'New York');

INSERT INTO books (id, title, author_id, publisher_id)
VALUES
    (1, 'Harry Potter', 1, 1),
    (2, '1984', 2, 1),
    (3, 'The Old Man and the Sea', 3, 2);

-- Join all three tables
SELECT
    books.title,
    authors.name AS author,
    authors.country,
    publishers.name AS publisher,
    publishers.city
FROM books
INNER JOIN authors ON books.author_id = authors.id
INNER JOIN publishers ON books.publisher_id = publishers.id
ORDER BY books.id;

Three tables, three rows out — each book matches exactly one author and exactly one publisher, so every book produces one surviving combination. Give any book a second author and the same query would return four rows without changing a character.

Aggregating with Joins

Combining a join with a summary is where row multiplication turns from a curiosity into a wrong number, so it is worth meeting here rather than being surprised by it later.

A bridge, because this section borrows from the next lesson. GROUP BY collapses the rows of a result into one row per distinct value of the column you name, and COUNT reports how many rows went into each of those groups. The next lesson, Aggregation and Grouping, is where that machinery is actually taught — what is being shown here is only what a join hands it, because the join is what decides which rows exist to be grouped in the first place.

-- Create categories table
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Create products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER,
    price_cents INTEGER
);

-- Insert categories. Books is stocked but has nothing in it yet.
INSERT INTO categories (id, name)
VALUES
    (1, 'Electronics'),
    (2, 'Furniture'),
    (3, 'Clothing'),
    (4, 'Books');

-- Insert products
INSERT INTO products (id, name, category_id, price_cents)
VALUES
    (1, 'Laptop', 1, 99999),
    (2, 'Mouse', 1, 2499),
    (3, 'Keyboard', 1, 7999),
    (4, 'Desk', 2, 29999),
    (5, 'Chair', 2, 19999),
    (6, 'T-Shirt', 3, 1999);

-- Count products per category. Watch the Books row in each of these.
SELECT
    categories.name AS category,
    COUNT(*) AS product_count
FROM categories
LEFT JOIN products ON categories.id = products.category_id
GROUP BY categories.name
ORDER BY categories.name;

SELECT
    categories.name AS category,
    COUNT(products.id) AS product_count
FROM categories
LEFT JOIN products ON categories.id = products.category_id
GROUP BY categories.name
ORDER BY categories.name;

Those two queries differ by three characters and they disagree about Books. The first reports 1 and the second reports 0, and the second is the true answer.

The reason is the manufactured row from the previous section. Books matched no product, so the LEFT JOIN emitted one row for it with every product column set to null. COUNT(*) counts rows, and that manufactured row is a row, so it counts as one. COUNT(products.id) counts non-null values of a named column, and the manufactured row's products.id is null, so it counts nothing and the group reports zero.

Neither query errors, neither warns, and the wrong one is the one most people write. Whenever an aggregate sits on top of a LEFT JOIN, name the column you actually mean.

Practical Example: E-commerce Database

-- Create a complete e-commerce schema
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    total_cents INTEGER
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price_cents INTEGER
);

-- Insert sample data
INSERT INTO customers (id, name, email)
VALUES (1, 'Alice', 'alice@example.com');

-- The order total is the sum of its line items:
-- 99999 + 2 * 2499 + 5 * 999 = 109992 cents.
INSERT INTO orders (id, customer_id, order_date, total_cents)
VALUES (1, 1, '2025-01-15', 109992);

INSERT INTO order_items (id, order_id, product_name, quantity, price_cents)
VALUES
    (1, 1, 'Laptop', 1, 99999),
    (2, 1, 'Mouse', 2, 2499),
    (3, 1, 'USB Cable', 5, 999);

-- Get complete order details
SELECT
    c.name AS customer,
    o.order_date,
    oi.product_name,
    oi.quantity,
    oi.price_cents,
    oi.quantity * oi.price_cents AS item_total_cents
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
ORDER BY oi.id;

One customer, one order, three line items — and three rows out, because the number of rows a chain of joins produces is the number of surviving combinations, which here is three. The customer's name and the order's date are repeated on every one of them.

That repetition is why the total_cents column on the order is a trap waiting for a careless query. It is stored once on the order row, and after the join it appears three times. Summing it across the joined rows gives 329976 — three times the real total — even though the order genuinely cost 109992, and every individual number in the result is correct.

Building the joined query one clause at a time

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'),(3,'Cy');
INSERT INTO orders VALUES (10,1,1200),(11,1,800),(12,2,400);

Three customers and three orders are already loaded, and Cy has ordered nothing. The five lines below are one query listing every customer beside each of their orders, keeping Cy visible.

When a Join Inflates a Total

This is the characteristic join bug and it never announces itself: the query runs, exits cleanly, and returns a number that is a whole multiple of the right one.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, credit_cents INTEGER NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada',5000),(2,'Bo',3000);
INSERT INTO orders VALUES (10,1,1200),(11,1,800),(12,2,400);
SELECT SUM(c.credit_cents) AS total_credit FROM customers c JOIN orders o ON o.customer_id = c.id;

2 row(s) affected
3 row(s) affected
total_credit
------------
8000

Try It Yourself

Here is the build task. The starter joins categories to products and reports a count, but a category is missing from the result and the count for another one is wrong. Work the three TODOs until every category is reported honestly.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL, price_cents INTEGER NOT NULL);
INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Clothing'),(4,'Books');
INSERT INTO products VALUES (1,'Laptop',1,99999),(2,'Mouse',1,2499),(3,'Desk',2,29999),(4,'T-Shirt',3,1999);
-- TODO 1: report every category, including one that stocks no products.
--         Electronics has 2 products; Books has 0 and must still appear.
-- TODO 2: make an empty category report 0 rather than 1.
--         Count something that does not exist on a manufactured row.
-- TODO 3: sort the result by category name, alphabetically.
SELECT c.name AS category, COUNT(*) AS product_count
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.name;

4 row(s) affected
4 row(s) affected
category | product_count
------------------------
Books | 0
Clothing | 1
Electronics | 2
Furniture | 1

Once it passes, try two variations, predicting the result before each Run. First, change COUNT(p.id) back to COUNT(*) while keeping the LEFT JOIN — predict what Books reports, and note that the row count of the result does not change at all, only one number inside it. Then add SUM(p.price_cents) AS total_cents to the select list and predict what Books shows in that column: None, because a sum over no values at all is null rather than zero, which is a second guard the same report needs.

A bridge, because the comparison below borrows from a later lesson. A SELECT written inside parentheses in the middle of another query is a subquery: the database runs the inner query first, collects the single column of values it returns, and then uses that collection as the list for the IN or NOT IN test in the outer query. So NOT IN (SELECT customer_id FROM orders) means "not equal to any of the customer_id values that the orders table actually holds" — the list is computed rather than typed out. Subqueries are taught properly at order 7, in Subqueries; what matters here is only that the second shape asks the same question as the first by a different route.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'),(3,'Cy');
INSERT INTO orders VALUES (10,1),(11,2);

SELECT c.id, c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

SELECT c.id, c.name FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders)
ORDER BY c.id;

INSERT INTO orders VALUES (12, NULL);

SELECT c.id, c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

SELECT c.id, c.name FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders)
ORDER BY c.id;

Practice Exercises

Try these exercises using the tables above:

1. Find all employees and their department locations
2. List all categories and count how many products each has, keeping the empty Books category in the result with a count of 0
3. Show each customer's total order amount, and say in advance how many rows the answer should have
4. Find all books by UK authors published by Penguin Books
5. List products with their category names, sorted by price with a tiebreaking second key

Pro Tip: When joining tables, always be clear about which columns come from which table. Use table aliases and always prefix column names (e.g., e.name instead of just name) to avoid confusion!

Key Takeaways

• Joins combine data from multiple related tables
• INNER JOIN returns only matching rows from both tables
• LEFT JOIN returns all rows from the left table, with NULLs for non-matching right table rows
• Use table aliases to make queries shorter and more readable
• The ON clause specifies how tables are related
• You can join multiple tables in one query
• Combine joins with WHERE, ORDER BY, and aggregate functions

Next Steps

With joins, you can pull related data from across your database into a single result set. But what if you need to answer questions like "how many orders did each customer place?" or "what is the average salary per department?" The next lesson covers aggregate functions (COUNT, SUM, AVG) and GROUP BY, which let you summarize and analyze data across groups of rows — the foundation of every reporting query you will ever write.