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:

1. Find all employees and their department locations
2. List all categories and count how many products each has (include categories with no products)
3. Show each customer's total order amount
4. Find all books by UK authors published by Penguin Books
5. 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.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

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!