ADVERTISEMENT

SQL EXISTS / ANY / ALL

These keywords are used with subqueries to perform advanced comparisons.
Let’s break them down simply:

🔹 EXISTS – Check if Subquery Returns Rows

Returns TRUE if the subquery returns at least one row.

SELECT department_name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.department_id
);

✅ Lists departments that have employees.

🔹 ANY – Compare with Any Matching Value

Returns TRUE if at least one value from the subquery meets the condition.

SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department_id = 50
);

✅ Fetches employees earning more than at least one employee in dept 50.

You can also use: = ANY (equivalent to IN), <> ANY, < ANY, etc.

🔹 ALL – Compare with All Values

Returns TRUE if the condition holds true for every value from the subquery.

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 50
);

✅ Fetches employees earning more than everyone in dept 50.

🧠 Quick Recap

KeywordPurposeReturns TRUE if…
EXISTSSubquery returns any rowAt least one row is found
ANYComparison true for at least oneCondition matches one or more values
ALLComparison true for every valueCondition matches all values in subquery

💡 Use these for powerful filtering when comparing against multiple rows.

ADVERTISEMENT