Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Combining Data

Subqueries

A subquery is a query inside another query. It lets you answer questions in steps — for example, “products more expensive than average”.

Beginner 5 min Lesson 14 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:WHEREAggregate Functions
0/31 in SQL Server
On this page · 5 min · Beginner

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Beginner
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

A question inside a question: “Show students whose marks are above (the class average).” The inner query finds the average first.

Subquery
A SELECT inside another query.
IN / EXISTS
Check against a subquery’s results.
Correlated
Inner query uses values from the outer row.

Learning objectives#

  • Use a scalar subquery in WHERE
  • Filter with IN and EXISTS
  • Find missing data with NOT EXISTS
  • Recognise correlated subqueries

Explanation#

A scalar subquery returns a single value and can be used wherever a value is expected.

IN (subquery) checks membership in a list; EXISTS (subquery) checks whether at least one matching row exists and stops at the first match.

A correlated subquery refers to the outer query’s row, so it is evaluated per row — for example, “orders for this customer”.

NOT EXISTS is the safest way to find rows with no match. NOT IN returns no rows if the subquery contains a NULL.

Code example: Above-average products and customers with no orders#

SQLAbove-average products and customers with no orders Illustrative
SELECT Name, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);


SELECT c.Name
FROM Customers AS c
WHERE NOT EXISTS (SELECT 1 FROM Orders AS o WHERE o.CustomerId = c.CustomerId);
Expected output
Name    Price
Laptop  55000.00

Name
Kabir

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. The inner query calculates the average price (about 12,046.67).
  2. Only the Laptop costs more, so it is the only row returned (the Desk at 12,000 is just below).
  3. The second query is correlated: for each customer it checks whether any order exists.
  4. Kabir has no orders, so NOT EXISTS is true for him.

Real-world use case#

Marketing finds “registered users who have never purchased” with NOT EXISTS to send a welcome offer.

Common mistakes#

NOT IN with NULLs

If the subquery returns any NULL, NOT IN returns no rows at all. Use NOT EXISTS.

Scalar subquery returning many rows

A subquery used with = must return one value, or you get “Subquery returned more than 1 value”.

Best practices#

  • Prefer EXISTS/NOT EXISTS for existence checks
  • Use SELECT 1 inside EXISTS — the columns are ignored
  • Consider a JOIN or CTE when a subquery becomes hard to read
  • Check execution plans for correlated subqueries on big tables

Remember this#

A subquery is a query inside a query.

EXISTS stops at the first match — often fastest.

Many subqueries can be rewritten as JOINs.

Practice exercise#

Your task: List products that have been ordered at least once, using IN.

Show solution
SQL Illustrative
SELECT Name FROM Products WHERE ProductId IN (SELECT ProductId FROM OrderItems);

Mini quiz#

Subqueries quizMultiple choice Question 1 of 1

Which is safest for “customers with no orders”?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.