Subquery Use Cases

Medium8 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have two tables.

Table: customers

customer_id	name	city
1	Ana	London
2	Raj	London
3	Mei	Paris

Table: orders

order_id	customer_id	order_date	total
10	1	2025-05-20	80
11	2	2024-11-02	45
12	3	2025-05-22	60

Task

What is a subquery? What is the difference between a correlated and an uncorrelated one?
Return every customer in London who placed an order in the last month. Assume today is 2025-06-01.

Expected output

customer_id	name	city
1	Ana	London

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

A subquery is a query nested inside another SQL query (such as SELECT, INSERT, UPDATE, or DELETE) to handle multi-step data operations and dynamic filtering.
Scalar Subquery: Returns a single row and a single column (one value).
Multi-Row Subquery: Returns a single column with multiple rows, often used with operators like IN, ANY, or ALL.
Correlated Subquery: References one or more columns from the outer query. It re-evaluates for each row processed by the main query.

1Basic

IN with an uncorrelated subquery

1SELECT *2FROM customers3WHERE city = 'London'4AND customer_id IN (5      SELECT customer_id6      FROM orders7      WHERE order_date >= DATE '2025-05-01'8            AND order_date < DATE '2025-06-01'9  );

SQL Server: use DATEADD(month, -1, GETDATE()) instead of the INTERVAL expression

2Optimized

EXISTS with a supporting index

1CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);2 3SELECT c.*4FROM customers c5WHERE c.city = 'London'6  AND EXISTS (SELECT 17              FROM orders o8              WHERE o.customer_id = c.customer_id9                AND o.order_date >= CURRENT_DATE - INTERVAL '1 month');10 11-- Safe "not in": customers with no orders at all12SELECT c.*13FROM customers c14WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Why: EXISTS stops at the first match and the index makes each lookup cheap
Modern planners often rewrite IN to a semi-join, so check EXPLAIN

Spotted a mistake in these answers?

Report a problem

Question

Problem

Hints

Answers

Related questions

2 ways to solve it

Quick answer