Subquery in WHERE in SQL

Level 4109 keystrokes

A subquery in WHERE compares each row against a calculated value.

SQL code

-- Return the name and salary of employees
1SELECT
2 name,
3 salary
4FROM
5 employees
-- Keep only employees who earn more than the average
6WHERE
7 salary > (
-- The subquery returns one value: the average salary of all employees
-- It does not depend on the outer row, so it only needs to be computed once
8 SELECT AVG(salary)
9 FROM employees
10 )
-- Highest salary first
11ORDER BY
12 salary DESC;

Output

+-------+--------+
| name  | salary |
+-------+--------+
| Carol | 85000  |
| Dave  | 72000  |
| Alice | 68000  |
+-------+--------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • (× 2
  • )× 2
  • ,× 1
  • ;× 1
  • >× 1

More SQL examples at level 4

← SQL typing practice