Subqueries in SQL

Queries Within Queries

Now that you’ve mastered joins, it’s time to take a step further into the world of SQL with subqueries. Subqueries, often referred to as nested queries, are like having a query inside another query. They allow you to break down complex problems into manageable steps, making your queries more powerful and flexible.

Let’s ease into the concept and build our understanding step by step.

What is a Subquery?

A subquery is a query that is embedded within another SQL query. It’s typically enclosed in parentheses and can be used in various places, such as the SELECT, FROM, or WHERE clauses.

Imagine you want to answer questions like:

• Which products have a price higher than the average price?
• Who ordered the most expensive product?

Subqueries are perfect for such tasks.

Scenario

Let’s revisit our familiar tables:

Products Table:

product_id	product_name	category	price	stock
1	Wireless Mouse	Electronics	1200	25
2	Yoga Mat	Fitness	800	50
3	Bluetooth Speaker	Electronics	2500	15
4	Water Bottle	Fitness	300	100
5	Smartwatch	Electronics	5000	10

Orders Table:

order_id	product_id	order_date	quantity
101	1	2025-01-01	2
102	2	2025-01-02	5
103	3	2025-01-02	1
104	5	2025-01-03	2
105	4	2025-01-03	10

We’ll use these tables to understand subqueries with real-world examples.

Subquery in the WHERE Clause

Let’s find products that cost more than the average price of all products.

SELECT product_name, price  
FROM products  
WHERE price > (SELECT AVG(price) FROM products);  

Okay. What happens here? Let’s see.

1. The subquery (SELECT AVG(price) FROM products) calculates the average price of all products.
2. The main query retrieves products where the price is greater than this average.

Result:

product_name	price
Bluetooth Speaker	2500
Smartwatch	5000

---

Subquery in the FROM Clause

Subqueries can also act like a temporary table. Let’s find the average price of each category.

SELECT category, AVG(price) AS avg_price  
FROM (  
    SELECT category, price  
    FROM products  
) AS category_prices  
GROUP BY category;  

Explanation:

1. The subquery selects category and price from the products table.
2. The main query calculates the average price for each category using GROUP BY.

Result:

category	avg_price
Electronics	2900
Fitness	550

Subquery in the SELECT Clause

What if you want to display each product’s price along with the overall average price?

SELECT product_name, price,  
       (SELECT AVG(price) FROM products) AS avg_price  
FROM products;  

1. The subquery calculates the overall average price.
2. This value is displayed alongside each product’s price.

Result:

product_name	price	avg_price
Wireless Mouse	1200	1400
Yoga Mat	800	1400
Bluetooth Speaker	2500	1400
Water Bottle	300	1400
Smartwatch	5000	1400

---

Subquery with IN Clause

Let’s find the names of products that have been ordered.

SELECT product_name  
FROM products  
WHERE product_id IN (SELECT product_id FROM orders);  

1. The subquery retrieves all product_id values from the orders table.
2. The main query finds matching products using IN.

Result:

product_name
Wireless Mouse
Yoga Mat
Bluetooth Speaker
Smartwatch
Water Bottle

---

Subquery with EXISTS

Now let’s find products that have not been ordered.

SELECT product_name  
FROM products p  
WHERE NOT EXISTS (  
    SELECT 1  
    FROM orders o  
    WHERE p.product_id = o.product_id  
);  

1. The subquery checks if a matching product_id exists in the orders table.
2. NOT EXISTS ensures only unmatched products are returned.

Result:
(No products in this example since all have been ordered.)

Correlated Subquery

A correlated subquery is a subquery that refers to the outer query.

Let’s find products whose order quantity is more than their stock.

SELECT product_name  
FROM products AS p  
WHERE EXISTS (  
    SELECT 1  
    FROM orders AS o  
    WHERE p.product_id = o.product_id  
      AND o.quantity > p.stock  
);  

1. The subquery checks for each product if its quantity in the orders table exceeds its stock.
2. The subquery is evaluated for each row in the outer query.

Summary

Subqueries are like SQL’s secret weapon—helping you solve complex problems step by step. Whether you’re filtering data with WHERE, creating temporary tables with FROM, or enhancing your output with SELECT, subqueries are indispensable.

In the next article, we’ll tackle how to insert, update or delete data from the table. Keep practicing!