Subqueries in SQL

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:

Orders Table:

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:

​

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:

​

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:

​

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:

​

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!