Subquery vs JOIN performance trade-off in SQL - Hands-On Comparison

Subquery vs JOIN Performance Trade-off

📖 Scenario: You are working as a database analyst for an online bookstore. You have two tables: books and sales. The books table contains book details, and the sales table records each sale with the book's ID and quantity sold.Your task is to find the total quantity sold for each book using two different SQL approaches: a subquery and a JOIN. This will help you understand the performance trade-offs between these methods.

🎯 Goal: Build two SQL queries to calculate total sales per book: one using a subquery and one using a JOIN. Compare their structure and understand when each is useful.

📋 What You'll Learn

Create a books table with columns book_id (integer) and title (text).

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

Write a query using a subquery to get each book's title and total quantity sold.

Write a query using a JOIN to get each book's title and total quantity sold.

💡 Why This Matters

🌍 Real World

This project simulates a common task in business analytics: summarizing sales data by product using different SQL techniques.

💼 Career

Understanding subqueries and JOINs is essential for database developers, analysts, and anyone working with relational databases to write efficient and clear queries.

1

Create the books and sales tables with sample data

Write SQL statements to create a books table with columns book_id (integer) and title (text), and a sales table with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows into books: (1, 'Learn SQL'), (2, 'Mastering Databases'), (3, 'Data Analysis Basics'). Insert these exact rows into sales: (1, 1, 3), (2, 2, 5), (3, 1, 2), (4, 3, 4).

SQL

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

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with the exact values given.

2

Write a subquery to calculate total quantity sold per book

Write a SQL query that selects title from books and uses a subquery to calculate the total quantity sold for each book. The subquery should sum quantity from sales where sales.book_id = books.book_id. Name the total quantity column total_sold.

SQL

SELECT title, (
    -- Your subquery here
) AS total_sold
FROM books;

Need a hint?

Use a subquery in the SELECT clause that sums quantity from sales where the book_id matches.

3

Write a JOIN query to calculate total quantity sold per book

Write a SQL query that selects title from books and joins the sales table on book_id. Use GROUP BY on title and calculate the total quantity sold using SUM(quantity). Name the total quantity column total_sold.

SQL

SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
JOIN sales ON -- Your join condition here
GROUP BY books.title;

Need a hint?

Use JOIN with ON books.book_id = sales.book_id. Then group by books.title and sum sales.quantity.

4

Add a query to show books with zero sales using LEFT JOIN

Write a SQL query that selects title from books and uses a LEFT JOIN with sales on book_id. Use COALESCE to show 0 for books with no sales in the total_sold column. Group by title.

SQL

SELECT books.title, COALESCE(SUM(sales.quantity), 0) AS total_sold
FROM books
LEFT JOIN sales ON books.book_id = sales.book_id
GROUP BY books.title;

Need a hint?

Use LEFT JOIN to include all books, even those without sales. Use COALESCE to replace NULL sums with 0.