Tutorials›SQL›Subqueries
🟢 Free Demo
SQL TutorialTopic 13 of 20

Subqueries

Use a query inside another query

✅ What You Will Learn

›What a subquery is and where it can be placed
›How to use subqueries in WHERE (scalar subquery)
›How to use subqueries in FROM (derived table)
›How to use EXISTS and NOT EXISTS
›When to use a subquery vs a JOIN

A subquery is a SELECT query written inside another query. The inner query runs first and its result is used by the outer query.

Subqueries let you use the result of one query as a filter, a value, or even a table in another query. They are essential for answering questions like "which customers spent more than the average?" or "which products sold more than last month?"

📋 The orders table used in examples

order_idcustomer_nameproductamountcity
1001Rahul SharmaLaptop45000Delhi
1002Priya VermaMobile Phone18000Noida
1003Amit KumarHeadphones3500Gurgaon
1004Sneha KapoorLaptop52000Delhi
1005Vikram SinghTablet28000Noida

Syntax

SQL SYNTAX
-- Subquery in WHERE
SELECT column
FROM table
WHERE column OPERATOR (SELECT column FROM table WHERE condition);

-- Subquery as a derived table in FROM
SELECT *
FROM (SELECT column, SUM(amount) FROM table GROUP BY column) AS summary;

Examples

Example 1Orders above the average order amount
SELECT customer_name, product, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
OUTPUT
customer_name | product | amount
--------------|---------|-------
Rahul Sharma  | Laptop  | 45000
Sneha Kapoor  | Laptop  | 52000
💡

The inner query calculates the average (29,625). The outer query filters rows above that value. The inner query runs first.

Example 2Customers who have placed orders (using IN)
SELECT customer_name, city
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
);
OUTPUT
customer_name | city
--------------|------
Rahul Sharma  | Noida
Priya Verma   | Delhi
💡

The subquery returns a list of customer_ids that appear in orders. The outer query finds customers whose ID is in that list.

Example 3Subquery in FROM — treating a query like a table
SELECT product, total_revenue
FROM (
  SELECT product, SUM(amount) AS total_revenue
  FROM orders
  GROUP BY product
) AS product_summary
WHERE total_revenue > 10000;
💡

The subquery creates a temporary result set (called product_summary) that the outer query then filters. This is called a derived table.

📌 Key Points to Remember

  • ✓The inner query (subquery) always runs first
  • ✓Subqueries in WHERE can return a single value or a list
  • ✓Use IN when the subquery returns multiple values
  • ✓Use = when the subquery returns exactly one value
  • ✓Subqueries in FROM must be given an alias

🏢 Real-World Application

Subqueries solve problems that a single query cannot. "Find all orders above the average order value" — you need the average first (inner query), then compare each order to it (outer query). "Find products that have never been returned" — EXISTS subquery. "Find customers who spent more than the top 10% threshold" — subquery to calculate the threshold, then outer query to filter. Subqueries are used heavily in financial analytics, fraud detection, and any situation where you need to compare each row against a dynamically computed value.

⚠️ Common Mistakes to Avoid

WRONGSubquery returning multiple rows in a scalar context
FIXIf a subquery in WHERE col = (subquery) returns more than one row, you get an error. Use IN instead of = when the subquery can return multiple values: WHERE col IN (SELECT ...).
WRONGCorrelated subquery running slowly on large tables
FIXA correlated subquery runs once per row in the outer query — extremely slow on millions of rows. Replace with a JOIN or CTE for better performance.
WRONGNot aliasing subqueries in FROM clause
FIXA subquery used as a derived table in FROM must be given an alias: FROM (SELECT ...) AS subquery_alias. Omitting the alias causes a syntax error.

❓ Frequently Asked Questions

What is a subquery in SQL?

A subquery is a SELECT query nested inside another query. The inner query runs first and its result is used by the outer query. Subqueries can appear in WHERE, SELECT, FROM, and HAVING clauses.

What is the difference between a subquery and a JOIN?

Both combine data from multiple sources. JOINs are generally faster and more readable for combining tables horizontally. Subqueries are useful when you need a calculated single value, or when the inner result is used as a filter.

What is a correlated subquery?

A correlated subquery references a column from the outer query. It runs once for every row in the outer query, making it potentially slow. Example: finding each employee's salary compared to their department's average.

What is the difference between IN and EXISTS in SQL subqueries?

IN checks if a value matches any value in the subquery result list. EXISTS checks if the subquery returns any rows at all (returns true/false). EXISTS is often faster when the subquery returns large result sets.

Can a subquery return multiple columns?

A subquery in WHERE must return a single column (for IN/= comparisons) or a single value (for scalar comparisons). A subquery in FROM (derived table) can return multiple columns.

✏️ Practice Exercise

Write a query to find all products whose total revenue is above the average product revenue across all products.

← PreviousLEFT JOINNext →SQL Aliases
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.