IN, EXISTS, ANY, ALL

Subquery Comparison Operators

IN

Checks if a value exists in the subquery result set.

SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

EXISTS

Checks if the subquery returns any rows (optimized for performance).

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

ANY and ALL

Used with comparison operators like >, <, = to compare against all or any values in subquery.

SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 30);