Subqueries: A Query Inside a Query
A subquery is a query inside another query. The inner query runs first, and the outer query uses its result.
You write the subquery inside parentheses ( ).
Syntax
SELECT column1 FROM table_name
WHERE column2 = (SELECT ... FROM ...);
Example 1: The Youngest Employees
Which employees have the smallest age? First you need the smallest age, then the employees with that age. A subquery does both in one query:
SELECT name, age FROM employees
WHERE age = (SELECT MIN(age) FROM employees);
Output:
name | age
------+-----
Mike | 24
Anna | 24
(2 rows)
The subquery (SELECT MIN(age) FROM employees) returns 24. So the outer query becomes WHERE age = 24.
Example 2: Subquery with IN
When a subquery returns a list of values, use IN. This finds the customers who have at least one delivered order:
SELECT name, city FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders
WHERE order_status = 'Delivered'
);
Output:
name | city
-------+----------
Alice | New York
Emma | London
(2 rows)
The subquery returns the customer ids 1 and 3. The outer query then shows those customers.
Tip
- A subquery used with
=,>or<must return one value. - A subquery used with
INcan return many values, but only one column.
In the exercise, find the employees who are older than the average age.