Subqueries in WHERE and FROM
A subquery (also called a nested query or inner query) is a query inside another query. The inner query runs first, and its result is used by the outer query. Subqueries can appear in WHERE, FROM, and SELECT clauses.
Subqueries in the WHERE clause are the most common. They let you filter results based on the output of another query, which is powerful when you cannot express the filter with a simple comparison.
- Scalar subquery: Returns a single value (one row, one column)
- Row subquery: Returns a single row with multiple columns
- Table subquery: Returns a full result set (multiple rows and columns)
- Subqueries are enclosed in parentheses
-- Subquery in WHERE: Find users older than the average age
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- Subquery with IN: Find customers who placed orders
SELECT name, email
FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);
-- Subquery in FROM (derived table)
SELECT avg_table.age_group, avg_table.user_count
FROM (
SELECT
CASE
WHEN age < 25 THEN 'Young'
WHEN age < 35 THEN 'Mid'
ELSE 'Senior'
END AS age_group,
COUNT(*) AS user_count
FROM users
GROUP BY age_group
) AS avg_table
WHERE avg_table.user_count > 1;- Subqueries in FROM (derived tables) must always have an alias. In the example above, we used AS avg_table.
Correlated Subqueries & EXISTS
A correlated subquery references columns from the outer query, which means it runs once for each row processed by the outer query. While powerful, correlated subqueries can be slow on large datasets.
The EXISTS operator checks whether a subquery returns any rows. It returns TRUE if the subquery produces at least one row, and FALSE otherwise. EXISTS is often more efficient than IN for checking relationships.
-- Correlated subquery: Find customers whose order total
-- exceeds the average order amount
SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.amount > (
SELECT AVG(amount) FROM orders
);
-- EXISTS: Find customers who have at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);
-- NOT EXISTS: Find customers with no orders
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);
-- Subquery in SELECT: Get order count per customer
SELECT
c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;- EXISTS is often faster than IN because it stops searching as soon as it finds a match, while IN must collect all results from the subquery first.