Subqueries

Subqueries

A subquery is a query nested inside another query. The inner query runs first, and its result feeds the outer query.

Subquery in WHERE

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
  SELECT CustomerID FROM Orders
);

Returns customers who have at least one order.

Subquery with a scalar result

SELECT ProductName, Price
FROM Products
WHERE Price = (
  SELECT MAX(Price) FROM Products
);

Returns the most expensive product.

Subquery in SELECT

SELECT CustomerName,
  (SELECT COUNT(*) FROM Orders
   WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

Where subqueries can appear

• In the WHERE clause (with IN, =, EXISTS, ANY, ALL).
• In the SELECT list (must return a single value).
• In the FROM clause (as a derived table).

Nested subqueries

Subqueries can contain subqueries, but keep them readable - deep nesting gets confusing fast.

TL;DR

• A subquery is a query inside another query.
• The inner query runs first.
• Used with IN, =, EXISTS, ANY, ALL.
• Can appear in WHERE, SELECT, and FROM.