Subqueries in WHERE clause in MySQL - Mini Project: Build & Apply

Filtering Products Using Subqueries in WHERE Clause

📖 Scenario: You work for an online store that sells various products. The store keeps track of products and their categories in a database. You want to find products that belong to categories with a specific condition.

🎯 Goal: Build a SQL query that uses a subquery inside the WHERE clause to filter products based on their category.

📋 What You'll Learn

Create a table called categories with columns category_id (integer) and category_name (varchar).

Create a table called products with columns product_id (integer), product_name (varchar), and category_id (integer).

Insert specific rows into both tables as given.

Write a SQL query that selects product names from products where the category_id is in the set of category IDs returned by a subquery filtering categories by name.

Use a subquery inside the WHERE clause.

💡 Why This Matters

🌍 Real World

Filtering products by category is a common task in e-commerce databases to show users relevant items.

💼 Career

Understanding subqueries in WHERE clauses is essential for database querying roles, data analysis, and backend development.

1

Create tables and insert data

Create a table called categories with columns category_id (integer) and category_name (varchar). Then create a table called products with columns product_id (integer), product_name (varchar), and category_id (integer). Insert these exact rows into categories: (1, 'Electronics'), (2, 'Books'), (3, 'Clothing'). Insert these exact rows into products: (101, 'Smartphone', 1), (102, 'Laptop', 1), (103, 'Novel', 2), (104, 'T-shirt', 3).

MySQL

-- Create categories table
-- Create products table
-- Insert data into categories
-- Insert data into products

Need a hint?

Use CREATE TABLE statements to define the tables. Use INSERT INTO to add the rows exactly as given.

2

Define the category filter

Create a variable or placeholder for the category name filter. For this project, define a string variable called filter_category and set it to 'Electronics'. This will be used to filter categories in the next step.

MySQL

CREATE TABLE categories (
  category_id INT,
  category_name VARCHAR(50)
);

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  category_id INT
);

INSERT INTO categories (category_id, category_name) VALUES
(1, 'Electronics'),
(2, 'Books'),
(3, 'Clothing');

INSERT INTO products (product_id, product_name, category_id) VALUES
(101, 'Smartphone', 1),
(102, 'Laptop', 1),
(103, 'Novel', 2),
(104, 'T-shirt', 3);

-- Define filter_category variable
-- Your code here

Need a hint?

Use SET @filter_category = 'Electronics'; to define the variable in MySQL.

3

Write the subquery in WHERE clause

Write a SQL query that selects product_name from products where the category_id is in the set of category_id values returned by a subquery. The subquery should select category_id from categories where category_name equals the variable @filter_category. Use the IN keyword with the subquery inside the WHERE clause.

MySQL

CREATE TABLE categories (
  category_id INT,
  category_name VARCHAR(50)
);

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  category_id INT
);

INSERT INTO categories (category_id, category_name) VALUES
(1, 'Electronics'),
(2, 'Books'),
(3, 'Clothing');

INSERT INTO products (product_id, product_name, category_id) VALUES
(101, 'Smartphone', 1),
(102, 'Laptop', 1),
(103, 'Novel', 2),
(104, 'T-shirt', 3);

SET @filter_category = 'Electronics';

-- Write the SELECT query with subquery in WHERE clause
-- Your code here

Need a hint?

Use WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = @filter_category) to filter products.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by product_name in ascending order. This completes the query to show filtered products sorted by name.

MySQL

CREATE TABLE categories (
  category_id INT,
  category_name VARCHAR(50)
);

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  category_id INT
);

INSERT INTO categories (category_id, category_name) VALUES
(1, 'Electronics'),
(2, 'Books'),
(3, 'Clothing');

INSERT INTO products (product_id, product_name, category_id) VALUES
(101, 'Smartphone', 1),
(102, 'Laptop', 1),
(103, 'Novel', 2),
(104, 'T-shirt', 3);

SET @filter_category = 'Electronics';

SELECT product_name FROM products
WHERE category_id IN (
  SELECT category_id FROM categories WHERE category_name = @filter_category
);

-- Add ORDER BY clause to sort by product_name
-- Your code here

Need a hint?

Add ORDER BY product_name ASC after the WHERE clause to sort results.