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 IN can return many values, but only one column.

In the exercise, find the employees who are older than the average age.