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

Need a 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

Need a 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

Need a 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

Need a hint?

Simply add comments above each query to clearly label them.