Mastering SQL Subqueries: Core Nested Query Techniques for Beginners

After mastering JOINs, learning subqueries becomes the next essential step for writing clear and efficient SQL.

What is a subquery?

A subquery is a query inside another query . The inner SELECT’s result is used as a condition or data source for the outer (main) query.

Core characteristic

Subqueries are executed first; their results are then supplied to the outer query.

Typical work scenarios

Batch filter data based on results from another table.

Aggregate first, then filter the aggregated data.

Replace simple JOINs to simplify the statement.

Batch match IDs, names, or conditional data.

Two main subquery categories

1. Single‑row single‑column subquery : returns one value for precise condition matching.

2. Multi‑row single‑column subquery : returns a set of values, typically used with IN for batch matching.

Practical examples

Example 1: Basic single‑row subquery

Requirement: Find all users who are the same age as "张三".

-- Subquery first finds 张三's age, outer query matches the same age

SELECT * FROM UserInfo

WHERE Age = (SELECT Age FROM UserInfo WHERE UserName='张三');

GO

Logic: first retrieve 张三's age, then directly match users with that age—no manual value entry needed.

Example 2: Multi‑row subquery with IN

Requirement: Retrieve information of all users who have placed an order.

-- Subquery finds all user IDs with orders, outer query matches those IDs

SELECT * FROM UserInfo

WHERE UserID IN (SELECT DISTINCT UserID FROM OrderInfo);

GO

Applicable scenario: when the subquery returns multiple results, IN must be used instead of =.

Example 3: Aggregate subquery (filter after statistics)

Requirement: Find users whose age is greater than the average age.

-- Subquery first calculates the overall average age

SELECT * FROM UserInfo

WHERE Age > (SELECT AVG(Age) FROM UserInfo);

GO

Example 4: Derived‑table subquery (temporary table)

Requirement: Group users by age, then keep only age groups with more than one person.

-- Subquery result acts as a temporary table and must have an alias

SELECT * FROM (

SELECT Age, COUNT(*) AS AgeTotal

FROM UserInfo

GROUP BY Age

) AS TempTable

WHERE AgeTotal > 1;

GO

Five essential rules for beginners

Use =, >, or < for single‑row subqueries; use IN for multi‑row subqueries, otherwise an error occurs.

When a subquery serves as a temporary table, it must be given an alias.

Subqueries can be nested, but avoid more than three levels to keep readability and performance acceptable.

Do not place an ORDER BY inside a subquery alone; sorting should be done in the outer query.

Prefer subqueries for simple matching; for complex multi‑table joins, use JOINs and combine both techniques flexibly.

Subquery vs. JOIN: How to choose

• Simple nesting with a single condition → subquery is more concise.

• Multi‑table, multi‑field associations or multi‑dimensional aggregations → JOINs are more efficient.

Use the strengths of each approach according to the query complexity.