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

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;

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;

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;

Hint

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

Practice

1. Which statement best describes the performance difference between a JOIN and a subquery in SQL?

easy

A. JOINs generally perform better because they combine tables in a single step.

B. Subqueries always perform better because they run separately.

C. JOINs and subqueries have the same performance in all cases.

D. Subqueries are faster because they use less memory.

Solution

1. Step 1: Understand how JOINs work

   JOINs combine rows from two or more tables in one operation, which is often optimized by the database engine.

2. Step 2: Compare with subqueries

   Subqueries run separately and then feed results to the main query, which can be slower especially with large data.

3. Final Answer:

   JOINs generally perform better because they combine tables in a single step. -> Option A

4. Quick Check:

   JOIN performance > Subquery performance [OK]

Hint: JOINs usually run faster than subqueries [OK]

Common Mistakes:

• Thinking subqueries always run faster
• Assuming JOINs and subqueries are always equal
• Believing subqueries use less memory

2. Which of the following SQL queries correctly uses a JOIN to get all customers and their orders?

easy

A. SELECT customers.name, orders.id FROM customers JOIN orders ON customers.id = orders.customer_id;

B. SELECT customers.name, orders.id FROM customers WHERE customers.id = orders.customer_id;

C. SELECT customers.name, orders.id FROM customers, orders WHERE customers.id == orders.customer_id;

D. SELECT customers.name, orders.id FROM customers JOIN orders ON customers.customer_id = orders.id;

Solution

1. Step 1: Check JOIN syntax

   Correct JOIN syntax uses ON with matching keys: customers.id = orders.customer_id.

2. Step 2: Validate each option

   SELECT customers.name, orders.id FROM customers JOIN orders ON customers.id = orders.customer_id; uses correct JOIN and ON condition. SELECT customers.name, orders.id FROM customers WHERE customers.id = orders.customer_id; uses WHERE without JOIN, which is invalid here. SELECT customers.name, orders.id FROM customers, orders WHERE customers.id == orders.customer_id; uses double equals (==) which is invalid in SQL. SELECT customers.name, orders.id FROM customers JOIN orders ON customers.customer_id = orders.id; reverses keys incorrectly.

3. Final Answer:

   SELECT customers.name, orders.id FROM customers JOIN orders ON customers.id = orders.customer_id; -> Option A

4. Quick Check:

   Correct JOIN syntax = SELECT customers.name, orders.id FROM customers JOIN orders ON customers.id = orders.customer_id; [OK]

Hint: JOIN uses ON with matching keys, not WHERE or == [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Using == instead of = in SQL
• Mixing up key columns in ON clause

3. Given the tables employees(id, name) and departments(id, name, manager_id), what will this query return?

SELECT e.name FROM employees e WHERE e.id IN (SELECT d.manager_id FROM departments d);

medium

A. Syntax error due to subquery.

B. Names of all employees regardless of department.

C. Names of employees who are not managers.

D. Names of employees who are managers of any department.

Solution

1. Step 1: Understand the subquery

   The subquery SELECT d.manager_id FROM departments d returns all manager IDs from departments.

2. Step 2: Analyze the main query

   The main query selects employee names where their ID is in the list of manager IDs, so it returns employees who manage departments.

3. Final Answer:

   Names of employees who are managers of any department. -> Option D

4. Quick Check:

   Subquery filters managers = Names of employees who are managers of any department. [OK]

Hint: IN with subquery filters matching IDs [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing managers with non-managers
• Assuming syntax error in subquery

4. Identify the error in this SQL query that uses a JOIN:

SELECT c.name, o.amount FROM customers c JOIN orders o WHERE c.id = o.customer_id;

medium

A. Incorrect table aliases used.

B. Using WHERE instead of HAVING for condition.

C. Missing ON keyword before join condition.

D. No error; query is correct.

Solution

1. Step 1: Review JOIN syntax

   JOIN requires an ON clause to specify join condition, not WHERE.

2. Step 2: Check the query

   The query uses WHERE for join condition, which is incorrect syntax for explicit JOIN.

3. Final Answer:

   Missing ON keyword before join condition. -> Option C

4. Quick Check:

   JOIN needs ON, not WHERE [OK]

Hint: JOIN must have ON clause for conditions [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN
• Confusing HAVING with WHERE
• Assuming aliases cause error

5. You want to list all products and their category names. The products table has category_id, and the categories table has id and name. Which approach is better for performance and why?

Options:
A) Use a JOIN to combine products and categories.
B) Use a subquery in SELECT to get category name for each product.
C) Use a subquery in WHERE to filter products by category name.
D) Use UNION to combine products and categories.

hard

A. Subquery in SELECT is better because it runs once per product.

B. JOIN is better because it retrieves all data in one step efficiently.

C. Subquery in WHERE is better because it filters early.

D. UNION is better because it merges tables.

Solution

1. Step 1: Understand the data retrieval goal

   You want product info with category names, which requires combining data from two tables.

2. Step 2: Compare approaches

   JOIN combines tables in one efficient operation. Subqueries in SELECT run once per row, causing slower performance. Subquery in WHERE filters but doesn't retrieve category names. UNION merges rows, not related here.

3. Final Answer:

   JOIN is better because it retrieves all data in one step efficiently. -> Option B

4. Quick Check:

   JOIN efficiency > subqueries for this task [OK]

Hint: JOIN combines tables efficiently for related data [OK]

Common Mistakes:

• Using subquery in SELECT causing slow per-row lookup
• Confusing UNION with JOIN
• Using subquery in WHERE without retrieving needed data