Join order and performance impact in SQL - Mini Project: Build & Apply

Join Order and Performance Impact

📖 Scenario: You work as a data analyst for an online bookstore. The database has two tables: books and sales. You want to find the titles of books sold along with the total quantity sold. You will explore how changing the order of joins can affect query performance.

🎯 Goal: Build SQL queries that join the books and sales tables in different orders and observe the impact on performance.

📋 What You'll Learn

Create the books table with columns book_id (integer), title (text), and author (text).

Create the sales table with columns sale_id (integer), book_id (integer), and quantity (integer).

Insert the specified sample data into both tables.

Write a SQL query joining books to sales to get book titles and total quantity sold.

Write a SQL query joining sales to books to get the same result.

Compare the two queries to understand join order impact.

💡 Why This Matters

🌍 Real World

Understanding join order helps optimize database queries in real business applications like sales reporting.

💼 Career

Database developers and analysts often tune queries by changing join order to improve speed and reduce resource use.

1

Create tables and insert data

Create the books table with columns book_id (integer), title (text), and author (text). Then create the sales table with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows into books: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee'). Insert these exact rows into sales: (101, 1, 3), (102, 2, 5), (103, 1, 2), (104, 3, 4).

SQL

-- Create tables and insert data as instructed
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with multiple rows for data.

2

Set up aggregation for total quantity

Create a SQL snippet that defines an aggregation to calculate total quantity sold per book. Use SUM(quantity) AS total_quantity in your SELECT statement. This will be used in the next step.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  book_id INTEGER,
  quantity INTEGER
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
  (101, 1, 3),
  (102, 2, 5),
  (103, 1, 2),
  (104, 3, 4);

-- Add SUM(quantity) AS total_quantity to use in next query
-- Your code here

Need a hint?

Use SUM(quantity) AS total_quantity in your SELECT clause to prepare for aggregation.

3

Write query joining books to sales

Write a SQL query that joins books to sales using books.book_id = sales.book_id. Select books.title and the total quantity sold as total_quantity. Group the results by books.title. Use the aggregation SUM(quantity) AS total_quantity.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  book_id INTEGER,
  quantity INTEGER
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
  (101, 1, 3),
  (102, 2, 5),
  (103, 1, 2),
  (104, 3, 4);

-- Write query joining books to sales
-- Your code here

Need a hint?

Use JOIN with books.book_id = sales.book_id. Select books.title and SUM(quantity) AS total_quantity. Group by books.title.

4

Write query joining sales to books

Write a SQL query that joins sales to books using sales.book_id = books.book_id. Select books.title and the total quantity sold as total_quantity. Group the results by books.title. Use the aggregation SUM(quantity) AS total_quantity. This query reverses the join order from the previous step.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  book_id INTEGER,
  quantity INTEGER
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
  (101, 1, 3),
  (102, 2, 5),
  (103, 1, 2),
  (104, 3, 4);

-- Write query joining sales to books
-- Your code here

Need a hint?

Use JOIN with sales.book_id = books.book_id. Select books.title and SUM(quantity) AS total_quantity. Group by books.title.