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.
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 with foreign key to customers
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
product TEXT,
amount DECIMAL(10, 2),
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, order_date)
VALUES
(1, 1, 'Laptop', 999.99, '2025-01-10'),
(2, 1, 'Mouse', 24.99, '2025-01-11'),
(3, 2, 'Keyboard', 79.99, '2025-01-12'),
(4, 3, 'Monitor', 299.99, '2025-01-13');
-- View the separate tables
SELECT * FROM customers;
SELECT * FROM orders;
INNER JOIN
INNER JOIN returns rows where there's a match in both tables.
-- Using the tables from above
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 DECIMAL(10, 2),
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, order_date)
VALUES
(1, 1, 'Laptop', 999.99, '2025-01-10'),
(2, 1, 'Mouse', 24.99, '2025-01-11'),
(3, 2, 'Keyboard', 79.99, '2025-01-12'),
(4, 3, 'Monitor', 299.99, '2025-01-13');
-- Join customers with their orders
SELECT
customers.name,
customers.email,
orders.product,
orders.amount,
orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
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 DECIMAL(10, 2)
);
-- 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)
VALUES
(1, 1, 'Laptop', 999.99),
(2, 1, 'Mouse', 24.99),
(3, 2, 'Keyboard', 79.99),
(4, 3, 'Monitor', 299.99);
-- Show all customers, even those without orders
SELECT
customers.name,
customers.city,
orders.product,
orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
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;
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;
Aggregating with Joins
Combine joins with aggregate functions to analyze related data:
-- 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 DECIMAL(10, 2)
);
-- Insert categories
INSERT INTO categories (id, name)
VALUES
(1, 'Electronics'),
(2, 'Furniture'),
(3, 'Clothing');
-- Insert products
INSERT INTO products (id, name, category_id, price)
VALUES
(1, 'Laptop', 1, 999.99),
(2, 'Mouse', 1, 24.99),
(3, 'Keyboard', 1, 79.99),
(4, 'Desk', 2, 299.99),
(5, 'Chair', 2, 199.99),
(6, 'T-Shirt', 3, 19.99);
-- Count products and average price per category
SELECT
categories.name AS category,
COUNT(products.id) AS product_count,
AVG(products.price) AS average_price
FROM categories
LEFT JOIN products ON categories.id = products.category_id
GROUP BY categories.name;
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 DECIMAL(10, 2)
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER,
product_name TEXT,
quantity INTEGER,
price DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO customers (id, name, email)
VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO orders (id, customer_id, order_date, total)
VALUES (1, 1, '2025-01-15', 1104.97);
INSERT INTO order_items (id, order_id, product_name, quantity, price)
VALUES
(1, 1, 'Laptop', 1, 999.99),
(2, 1, 'Mouse', 2, 24.99),
(3, 1, 'USB Cable', 5, 9.99);
-- Get complete order details
SELECT
c.name AS customer,
c.email,
o.order_date,
oi.product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS item_total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id;
Practice Exercises
Try these exercises using the tables above:
- Find all employees and their department locations
- List all categories and count how many products each has (include categories with no products)
- Show each customer's total order amount
- Find all books by UK authors published by Penguin Books
- List products with their category names, sorted by price
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.nameinstead of justname) 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
Congratulations! You've completed the SQL basics. You now know how to:
- Select and filter data
- Insert, update, and delete records
- Join multiple tables together
- Organize and analyze your data
Keep practicing these concepts, and you'll be writing complex SQL queries in no time!