Back to Topics

Subqueries

Advanced
14 min

What is Subqueries?

A subquery is a query nested inside another query, allowing you to use the result of one query in another query.

Key Components:

  • Query inside another query
  • Can be used in SELECT, FROM, WHERE
  • Returns single value or list
  • Powerful for complex filtering

Why it matters

Comparative Analysis

Find employees earning above average

List Matching

Customers who ordered specific products

Exists Testing

Check existence of related records

Complex Logic

Multi-step filtering

Key Concepts

IN

Checks if value exists in subquery

Example: WHERE ID IN (SELECT...)...

EXISTS

Checks if subquery returns rows

Example: WHERE EXISTS (SELECT 1...)...

Scalar

Returns single value

Example: (SELECT AVG(Sales))...

Correlated

References outer query

Example: WHERE Sales > (SELECT AVG FROM same table)...

How to use

1

Write outer query

Main query structure

2

Add WHERE condition

Use IN or EXISTS

3

Write subquery

Enclose in parentheses

4

Ensure single column

Subquery returns one column

5

Test separately

Test subquery alone first

6

Combine

Put subquery in main query

Example

Goal: Find customers who placed orders
SELECT * FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders)
Result: Only customers who have placed at least one order

Pro Tips

  • •Test subquery first: Ensure it works alone
  • •Use EXISTS for performance: Faster than IN
  • •Avoid deep nesting: Hard to read and maintain

Practice

Find products that cost more than the average product price
PreviousNext: Window Functions