SQL EXISTS / ANY / ALL

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

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

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