What is a Subquery in SQL?

✅ A subquery is:

A query inside another query.

• It’s enclosed in parentheses (...).
• It is executed first, and its result is used by the outer query.
• It can return:
  • A single value (scalar).
  • A row.
  • A table (multiple rows and columns).

Where Can You Use a Subquery?

You can use subqueries in:

Clause	Example
SELECT list	In column selection
FROM clause	As a derived table
WHERE clause	To filter rows
HAVING clause	To filter groups

Simple Example

Suppose you have a table Products:

product_id	name	price
1	Phone	800
2	Laptop	1200
3	Tablet	600

Find products that are more expensive than the average price.

Step-by-step:

1. First, calculate the average price:

SELECT AVG(price) FROM Products;

Then, find products with price > average:

SELECT name, price
FROM Products
WHERE price > (SELECT AVG(price) FROM Products);

✅ The part:

(SELECT AVG(price) FROM Products)

is a subquery — it returns a single value (the average price) and is used in the WHERE clause.

Types of Subqueries

Type	Description
Scalar Subquery	Returns a single value (one row, one column).
Row Subquery	Returns a single row (multiple columns).
Table Subquery	Returns a full table (multiple rows, multiple columns).
Correlated Subquery	Refers to columns from the outer query. Runs row-by-row.

What is a Correlated Subquery?

A correlated subquery depends on the outer query — it runs once for each row.

Example:
Find customers who placed more orders than average for their city:

SELECT c.customer_id, c.city
FROM Customers c
WHERE 
    (SELECT COUNT(*)
     FROM Orders o
     WHERE o.customer_id = c.customer_id) 
    > 5;

Here, the subquery depends on c.customer_id from the outer query.

It’s executed once for each customer — correlated.

Key Points About Subqueries

Feature	Details
Executed first	Inner query runs before the outer query.
Can be used in many clauses	SELECT, FROM, WHERE, HAVING.
Helps build complex logic	Breaks down complex problems.
Scalar, Row, or Table	Can return different amounts of data.
Correlated vs Non-correlated	Correlated re-executes per row; non-correlated runs once.

In Short

Subquery = Query inside another query — used to get values or tables dynamically inside your SQL statements.