13.MySQL EXISTS, ANY, and ALL: Deep Dive Tutorial with Real Examples

When working with advanced SQL logic, subqueries play a crucial role. Three powerful keywords that extend the utility of subqueries in MySQL are EXISTS, ANY, and ALL. These allow you to perform highly conditional queries and filter data based on the presence or comparison of related data sets.

In this tutorial, we’ll use the following sample data to explore each keyword with clarity and depth.

👨‍💼 employees Table

🏢 departments Table

💻 laptops Table

✅ MySQL EXISTS

The EXISTS keyword checks for the existence of rows returned by a subquery. It returns TRUE if the subquery returns any rows.

🔹 Example 1: List departments that have at least one employee

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

Explanation: For each department, the subquery checks if at least one employee exists in that department.

Result:

Marketing is excluded as it has no employees.

🔹 Example 2: List employees who have laptops

SELECT name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM laptops l
  WHERE l.employee_id = e.employee_id
);

Result:

David has no laptop, so he is excluded.

✅ MySQL ANY

The ANY keyword compares a value to any value returned by a subquery. It’s often used with =, >, <, >=, <=, or !=.

🔹 Example 3: Employees in departments larger than department 1

SELECT name
FROM employees
WHERE department_id = ANY (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) > (
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 1
  )
);

Explanation:

• The inner subquery counts employees in each department.
• Compares those counts against the size of department 1 (HR).
• The outer query returns employees from any department larger than HR.

Result:

✅ MySQL ALL

The ALL keyword compares a value to all values returned by a subquery. It must satisfy the condition for every returned value.

🔹 Example 4: Employees from departments with equal or higher employee count than all other departments

SELECT name
FROM employees
WHERE department_id = ALL (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) <= (
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 2
  )
);

Explanation:

• IT has 2 employees. This query returns employees from departments that have employee count ≤ IT.
• ALL ensures that comparison is made against every other department.

Result:

✅ Summary Table

✅ Practice Tasks

1. Use EXISTS to list employees who have a manager.
2. Use ANY to find employees from departments that have more employees than HR.
3. Use ALL to find departments that have fewer employees than all others.

🧠 Pro Tips

• EXISTS is faster than IN when the subquery returns many rows.
• Use indexed columns inside subqueries for better performance.
• Combine EXISTS with NOT to find non-matching sets.