Play Store Application link – SQL in 18 steps – App on Google Play

Introduction

Imagine you run a bookstore and want to find books that are more expensive than the average book price. Instead of first calculating the average price manually, then checking books above that price, SQL allows you to do both in one go using Subqueries.

A Subquery is simply a query inside another query. It helps break complex problems into smaller, more manageable queries.

1. Single-Row Subqueries – One Result Only

These subqueries return only one value, which is then used in the outer query.

Example Scenario:

Find books that are more expensive than the average book price.

SELECT title, price 
FROM Books
WHERE price > (SELECT AVG(price) FROM Books);

Here, (SELECT AVG(price) FROM Books) calculates the average price, and the outer query fetches books that are costlier than that.

2. Multi-Row Subqueries – Multiple Values Returned

These return multiple values and are usually paired with IN, ANY, or ALL.

Example Scenario:

Find customers who have placed orders.

SELECT name 
FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Orders);

Here, the subquery retrieves all customer_ids from the Orders table, and the outer query selects customers whose customer_id is in that list.

3. Correlated Subqueries – Row-by-Row Execution

A correlated subquery runs once for each row in the outer query. It depends on the outer query for its values.

Example Scenario:

Find employees earning more than the average salary of their department.

SELECT name, salary, department_id
FROM Employees e1
WHERE salary > (SELECT AVG(salary) FROM Employees e2 WHERE e1.department_id = e2.department_id);

Here, the inner query calculates the average salary for each department, and the outer query filters employees accordingly.

4. EXISTS and NOT EXISTS – Checking for Data Presence

Used when we only check for the existence of records, not actual values.

Example Scenario:

Find customers who have placed at least one order.

SELECT name
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE c.customer_id = o.customer_id);

The subquery checks if at least one order exists for the customer. SELECT 1 is used because we only care about existence, not specific values.

To find customers who haven’t placed orders, use NOT EXISTS:

SELECT name
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE c.customer_id = o.customer_id);

5. Database-Specific Variations

Different databases handle subqueries slightly differently:

Conclusion

Subqueries make SQL more powerful by allowing complex filtering and data retrieval within a single query. They are great for breaking down problems but can sometimes be performance-heavy, so use them wisely!