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

sql

### SELECT CustomerName

### FROM Customers

### WHERE CustomerID IN (

### SELECT CustomerID FROM Orders
);

Returns customers who have at least one order.

Subquery with a scalar result

sql

### SELECT ProductName, Price

### FROM Products

### WHERE Price = (

### SELECT MAX(Price) FROM Products
);

Returns the most expensive product.

Subquery in SELECT

sql

### 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.