SQL EXISTS, ANY and ALL

EXISTS asks a yes/no question: “is there at least one matching row?” It is the clearest and safest way to filter by the presence or absence of related data. ANY and ALL compare one value with every value a subquery returns: “more than any sales salary”, “more than all of them”.

EXISTS syntax

SELECT columnsFROM table_a aWHERE EXISTS (SELECT 1 FROM table_b b WHERE b.key = a.key);

The subquery’s select list does not matter: EXISTS only checks whether any row comes back. SELECT 1 is the usual convention.

Sample data

EXISTS: rows that have a match

Customers with at least one order:

SELECT c.customer_id, c.customer_nameFROM customers cWHERE EXISTS (  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)ORDER BY c.customer_id;

NOT EXISTS: rows without a match

Customers who have never ordered:

SELECT c.customer_id, c.customer_nameFROM customers cWHERE NOT EXISTS (  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Add conditions inside the subquery for more specific questions. Customers with no delivered order:

SELECT c.customer_nameFROM customers cWHERE NOT EXISTS (  SELECT 1 FROM orders o  WHERE o.customer_id = c.customer_id AND o.status = 'Delivered')ORDER BY c.customer_name;

NOT EXISTS vs NOT IN with NULLs

Who manages nobody? With NOT IN and a subquery that contains a NULL, the answer is silently empty:

SELECT first_nameFROM employeesWHERE emp_id NOT IN (SELECT manager_id FROM employees);

NOT EXISTS gives the right answer, because it only checks for matching rows:

SELECT e.first_nameFROM employees eWHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)ORDER BY e.emp_id;

ANY / SOME

value > ANY (subquery) is true if the value is greater than at least one value in the list. Employees who earn more than at least one sales employee:

SELECT first_name, salaryFROM employeesWHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 2)  AND dept_id <> 2ORDER BY salary;

ALL

value > ALL (subquery) is true only if the value is greater than every value in the list:

SELECT first_name, salaryFROM employeesWHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2)ORDER BY salary;

SELECT p.product_nameFROM products pWHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id)ORDER BY p.product_id;

SELECT d.dept_nameFROM departments dWHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

EXISTS FAQ