EXISTS, ANY, and ALL

Objectives

By the end of this lesson, you should be able to:

• Check whether a subquery returns any rows at all with EXISTS
• Compare a value against every row a subquery returns with ANY and ALL
• Explain the difference between ANY and ALL

💡 Why this matters: “Does this department have anyone earning over 90000” only needs a yes/no answer, not the actual matching rows, EXISTS answers exactly that, often more efficiently than pulling back real data just to check if any exists.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier lessons.

EXISTS

SELECT DISTINCT department FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees e2 WHERE e2.department = e.department AND e2.salary > 90000
)
ORDER BY department;

 department
--------------
 Engineering

EXISTS (subquery) returns true if the subquery returns at least one row, false if it returns none, the actual column values the subquery selects don’t matter (SELECT 1 is a common convention, signaling “the content doesn’t matter, only whether a row exists”). This is a correlated subquery (Lesson 2), checking, for each department, whether any employee there earns over 90000. Only Engineering has one (Taylor, at 95000), so it’s the only department in the result.

ANY

SELECT first_name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales')
ORDER BY first_name;

 first_name |  salary
------------+----------
 Alexis     | 71000.00
 Erin       | 74000.00
 Maya       | 67000.00
 Priya      | 89000.00
 Sam        | 63000.00
 Taylor     | 95000.00

> ANY (subquery) is true if the comparison holds against at least one value the subquery returns. Sales salaries are 61000 and 63000, so salary > ANY (...) means “greater than at least one of those,” which is true for anyone earning more than the lower one, 61000. This is why Sam (63000, in Sales himself) still qualifies, 63000 is greater than 61000, satisfying ANY.

ALL

SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales')
ORDER BY first_name;

 first_name |  salary
------------+----------
 Alexis     | 71000.00
 Erin       | 74000.00
 Maya       | 67000.00
 Priya      | 89000.00
 Taylor     | 95000.00

> ALL (subquery) is true only if the comparison holds against every value the subquery returns, the stricter version. Sam (63000) drops out of this result, 63000 isn’t greater than both Sales salaries (61000 and 63000, and a value isn’t greater than itself). > ALL (...) here effectively means “greater than the maximum,” > ANY (...) means “greater than the minimum.”

Try It

1. Write a query using EXISTS to find every department that has at least one employee hired before 2020.
2. Write a query using ANY to find every employee earning more than at least one Marketing employee.
3. Write the same question using ALL instead, and compare the two result sets.
4. Explain, in your own words, why salary > ALL (subquery) is equivalent to salary > (SELECT MAX(salary) FROM ...), and why salary > ANY (subquery) is equivalent to salary > (SELECT MIN(salary) FROM ...).

Recap

• EXISTS (subquery) checks only whether any row comes back, true or false, the subquery’s actual columns don’t matter.
• > ANY (subquery) is true if the comparison holds against at least one returned value, equivalent to comparing against the minimum for >.
• > ALL (subquery) is true only if the comparison holds against every returned value, equivalent to comparing against the maximum for >.

Next lesson: UNION, UNION ALL, INTERSECT, and EXCEPT, combining the results of separate queries.