Subqueries vs JOINs comparison in MySQL - Hands-On Comparison

Subqueries vs JOINs Comparison in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to find out which books have been ordered by customers and also get the customer names who ordered them.

🎯 Goal: Build two SQL queries: one using a subquery and one using a JOIN to list book titles along with the names of customers who ordered them.

📋 What You'll Learn

Create a table books with columns book_id and title

Create a table orders with columns order_id, book_id, and customer_name

Write a subquery to select book titles and customer names

Write a JOIN query to select the same data using INNER JOIN

💡 Why This Matters

🌍 Real World

Databases often store related data in multiple tables. Knowing how to retrieve combined information using subqueries or JOINs is essential for reports and applications.

💼 Career

Database developers and analysts frequently write queries using subqueries and JOINs to extract meaningful insights from relational data.

1

Create the books and orders tables with sample data

Create a table called books with columns book_id (integer) and title (varchar). Insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'). Then create a table called orders with columns order_id (integer), book_id (integer), and customer_name (varchar). Insert these exact rows: (101, 1, 'Alice'), (102, 2, 'Bob'), (103, 1, 'Charlie').

MySQL

-- Create books table and insert data
-- Create orders table and insert data
-- Your code here

Hint

Use CREATE TABLE statements for both tables and INSERT INTO to add the rows exactly as given.

2

Write a subquery to find book titles and customer names

Write a SELECT query that retrieves title from books and customer_name from orders using a subquery. Use a WHERE clause with a subquery that selects book_id from orders matching books.book_id. Name this query subquery_result.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100)
);

INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE orders (
  order_id INT,
  book_id INT,
  customer_name VARCHAR(100)
);

INSERT INTO orders (order_id, book_id, customer_name) VALUES
(101, 1, 'Alice'),
(102, 2, 'Bob'),
(103, 1, 'Charlie');

-- Write a SELECT query using a subquery to get title and customer_name
-- Your code here

Hint

Use a subquery in the SELECT clause or WHERE clause to link books and orders.

3

Write a JOIN query to find book titles and customer names

Write a SELECT query that retrieves title from books and customer_name from orders using an INNER JOIN on book_id. Name this query join_result.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100)
);

INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE orders (
  order_id INT,
  book_id INT,
  customer_name VARCHAR(100)
);

INSERT INTO orders (order_id, book_id, customer_name) VALUES
(101, 1, 'Alice'),
(102, 2, 'Bob'),
(103, 1, 'Charlie');

-- Write a SELECT query using INNER JOIN to get title and customer_name
-- Your code here

Hint

Use INNER JOIN with ON books.book_id = orders.book_id to combine the tables.

4

Compare the results and finalize the queries

Add comments above each query to label them as -- Subquery Result and -- JOIN Result. Ensure both queries are present in the code and correctly formatted.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100)
);

INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE orders (
  order_id INT,
  book_id INT,
  customer_name VARCHAR(100)
);

INSERT INTO orders (order_id, book_id, customer_name) VALUES
(101, 1, 'Alice'),
(102, 2, 'Bob'),
(103, 1, 'Charlie');

-- Add comments labeling the queries
-- Your code here

Hint

Simply add comments above each query to clearly label them.

Practice

1. Which of the following best describes the main difference between a JOIN and a subquery in MySQL?

easy

A. JOIN runs inside another query; subqueries combine tables side-by-side.

B. JOIN is used only for filtering; subqueries are used only for combining tables.

C. JOIN combines tables side-by-side; subqueries run inside another query to filter or find values.

D. JOIN and subqueries are exactly the same in functionality and performance.

Solution

1. Step 1: Understand the role of JOIN

   A JOIN combines rows from two or more tables side-by-side based on a related column between them.

2. Step 2: Understand the role of subqueries

   A subquery is a query nested inside another query, often used to filter or find specific values before the main query runs.

3. Final Answer:

   JOIN combines tables side-by-side; subqueries run inside another query to filter or find values. -> Option C

4. Quick Check:

   JOIN = combine tables, Subquery = nested query [OK]

Hint: JOINs combine tables; subqueries nest queries inside [OK]

Common Mistakes:

• Confusing JOINs as nested queries
• Thinking subqueries combine tables side-by-side
• Believing JOINs and subqueries have identical performance always

2. Which of the following is the correct syntax to write a JOIN between tables employees and departments on the column department_id?

easy

A. SELECT * FROM employees JOIN departments USING employees.department_id;

B. SELECT * FROM employees JOIN departments WHERE employees.department_id = departments.department_id;

C. SELECT * FROM employees, departments ON employees.department_id = departments.department_id;

D. SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

Solution

1. Step 1: Identify correct JOIN syntax

   The correct JOIN syntax uses the ON keyword to specify the matching columns between tables.

2. Step 2: Check each option

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; uses ON correctly. SELECT * FROM employees JOIN departments WHERE employees.department_id = departments.department_id; incorrectly uses WHERE instead of ON. SELECT * FROM employees, departments ON employees.department_id = departments.department_id; uses comma join but incorrectly adds ON. SELECT * FROM employees JOIN departments USING employees.department_id; misuses USING with full column name.

3. Final Answer:

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; -> Option D

4. Quick Check:

   JOIN requires ON for matching columns [OK]

Hint: Use ON to specify join condition, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Using comma join with ON clause
• Incorrect USING syntax with full column names

3. Given the tables orders(order_id, customer_id, amount) and customers(customer_id, name), what will the following query return?

SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 100);

medium

A. Names of customers who have orders with amount greater than 100.

B. All customer names regardless of orders.

C. Names of customers who have no orders.

D. Syntax error due to subquery.

Solution

1. Step 1: Analyze the subquery

   The subquery selects customer_id from orders where amount is greater than 100.

2. Step 2: Analyze the main query

   The main query selects names from customers where customer_id is in the list returned by the subquery.

3. Final Answer:

   Names of customers who have orders with amount greater than 100. -> Option A

4. Quick Check:

   Subquery filters customers by order amount [OK]

Hint: IN with subquery filters matching rows [OK]

Common Mistakes:

• Assuming all customers are returned
• Thinking it returns customers without orders
• Believing subquery causes syntax error

4. Identify the error in the following query that tries to list all employees with their department names:

SELECT e.name, d.department_name FROM employees e JOIN departments d WHERE e.department_id = d.department_id;

medium

A. Using WHERE instead of HAVING for join condition.

B. Missing ON keyword before the join condition.

C. Incorrect table aliases used.

D. No error; query is correct.

Solution

1. Step 1: Check JOIN syntax

   JOIN requires the ON keyword to specify the join condition between tables.

2. Step 2: Identify the mistake

   The query uses WHERE instead of ON to specify the join condition, which is incorrect syntax.

3. Final Answer:

   Missing ON keyword before the join condition. -> Option B

4. Quick Check:

   JOIN needs ON, not WHERE, for conditions [OK]

Hint: JOIN conditions must use ON, not WHERE [OK]

Common Mistakes:

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

5. You want to find all customers who have placed orders but only want to use a JOIN instead of a subquery. Which query correctly replaces this subquery?

SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

hard

A. SELECT DISTINCT c.name FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

B. SELECT c.name FROM customers c JOIN orders o WHERE c.customer_id = o.customer_id;

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

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

Solution

1. Step 1: Understand the original subquery

   The subquery selects customer_ids from orders, filtering customers who placed orders.

2. Step 2: Translate to JOIN

   Using JOIN on customer_id between customers and orders returns matching customers. DISTINCT is needed to avoid duplicates.

3. Step 3: Evaluate options

   SELECT DISTINCT c.name FROM customers c JOIN orders o ON c.customer_id = o.customer_id; uses JOIN with ON and DISTINCT correctly. SELECT c.name FROM customers c JOIN orders o WHERE c.customer_id = o.customer_id; misses ON keyword. SELECT c.name FROM customers c, orders o WHERE c.customer_id = o.customer_id; uses old-style join but is valid but may return duplicates. SELECT name FROM customers LEFT JOIN orders ON customer_id = customer_id; uses LEFT JOIN but ON condition is ambiguous.

4. Final Answer:

   SELECT DISTINCT c.name FROM customers c JOIN orders o ON c.customer_id = o.customer_id; -> Option A

5. Quick Check:

   JOIN with ON and DISTINCT matches subquery result [OK]

Hint: Use JOIN with ON and DISTINCT to replace IN subquery [OK]

Common Mistakes:

• Omitting ON keyword in JOIN
• Not using DISTINCT causing duplicates
• Using ambiguous ON conditions