Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Nested Query

Nested Query

Learn about the Nested query pattern, which uses one query inside another for dynamic filtering, calculations, and comparisons in SQL.

We'll cover the following...

When a customer service analyst wants to find all customers who ordered the most expensive product in the store, a basic query won’t cut it. We need to first find the maximum price, and then check who bought it—a task that requires one query to be embedded within another. This is where Nested queries, also known as subqueries, become essential. Nested queries help us solve problems where one result depends on another, and they appear in everything from fraud detection to leaderboards to filtering by computed metrics.

In this lesson, we will:

  • Explore the power and flexibility of nested queries.

  • Be able to filter records based on dynamic criteria, calculate values based on inner queries, and understand how subqueries make our code more readable and efficient.

  • Work through practical examples, study how they’re structured, and get plenty of hands-on practice.

Pattern overview

Category:

  • Comparison Patterns

Intent:

Use a query inside another query to perform conditional logic, filtering, or calculations based on dynamic results.

Motivation:

Sometimes we don’t know the value we need to filter or compare against ahead of time. Nested queries allow us to compute this value on the fly and use it directly in our outer query. This is key for writing flexible, reusable, and intelligent SQL.

Also known as:

  • Subqueries

  • Inner Queries

  • Correlated Subqueries (for row-wise comparisons)

Structure

There are two common forms of nested queries:

1. Scalar subquery: It returns a single value, usually for filtering or comparison.

SELECT ...
FROM   ...
WHERE  column = (SELECT MAX(column) FROM table);
Syntax for scalar subquery

2. Table subquery: It is also known as correlated subquery. It returns a list or set of values, used with IN, EXISTS, or JOIN.

SELECT ...
FROM   ...
WHERE  column IN (SELECT column FROM table WHERE condition);
Syntax for table subquery

Subqueries can appear in:

  • WHERE clause

  • SELECT clause

  • FROM clause (as derived tables)

Keywords

SELECT, IN, EXISTS, =, <, >, ANY, ALL, JOIN, WHERE, GROUP BY, HAVING

Problem structure

We use the Nested Query pattern when:

  • We need to dynamically identify a value or set of values (e.g., max price, top customer, latest date).

  • We write a subquery to compute that value or result set.

  • We use the subquery in the outer query to filter, compare, or compute additional logic.

  • We choose between a scalar subquery (single value) or a table subquery (multiple rows/columns) depending on the use case.

  • Look for keywords like: “based on max,” “use result from another query,” “latest record,” “top item,” or “dynamic filter” to identify when this pattern applies.

Example use cases

1. Products that match the highest price.

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products. Write an SQL query to find the name and price of the product(s) with the highest price.

If multiple products share the same highest price, return all of them.

Press + to interact
-- Products priced at the maximum price
SELECT ProductName, Price
FROM   Products
WHERE  Price = (
        SELECT MAX(Price)
        FROM   Products
       );

2. Customers who placed more than the average number of orders.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed. Write an SQL query to return the CustomerIDs of customers who have placed more orders than the average number of orders per customer.

Retur ...