SQL EXISTS, ANY, and ALL Operators

The EXISTS, ANY, and ALL operators are used with subqueries to compare values or test for the existence of records. They allow writing more expressive conditions that work with a set of values returned by an inner query.

The Reference Tables

Students Table

Scholarships Table

The EXISTS Operator

EXISTS checks whether a subquery returns at least one row. If the subquery returns any row (even an empty one), the condition is true. It does not care about the actual values — only whether rows exist.

EXISTS is typically used as a more efficient alternative to IN when checking for the presence of related records across tables.

Syntax

SELECT column1
FROM table1
WHERE EXISTS (
    SELECT 1 FROM table2 WHERE condition
);

Example 1: Find Students Who Have a Scholarship

SELECT StudentName, City, Fees
FROM Students s
WHERE EXISTS (
    SELECT 1 FROM Scholarships sc
    WHERE sc.StudentID = s.StudentID
);

How it works: For each student in the outer query, the inner query checks whether a row exists in the Scholarships table with a matching StudentID. If yes, the student is included in the result.

Result:

Example 2: NOT EXISTS — Students Without a Scholarship

SELECT StudentName, City
FROM Students s
WHERE NOT EXISTS (
    SELECT 1 FROM Scholarships sc
    WHERE sc.StudentID = s.StudentID
);

Result: Priya Mehta and Sneha Kapoor — no scholarship records found for them.

EXISTS vs IN — The Key Difference

The ANY Operator

ANY compares a value to each value in a set returned by a subquery. The condition is true if the comparison holds for at least one value in the set.

ANY is always used with a comparison operator: =, >, <, >=, <=, or !=.

Syntax

SELECT column1
FROM table1
WHERE column1 operator ANY (SELECT column FROM table2 WHERE condition);

Example 3: Find Students Whose Fees Are Greater Than ANY Commerce Student's Fees

SELECT StudentName, Course, Fees
FROM Students
WHERE Fees > ANY (
    SELECT Fees FROM Students WHERE Course = 'Commerce'
);

Inner query result (Commerce fees): 52000, 60000

Condition becomes: Fees > 52000 OR Fees > 60000 — i.e., Fees must be greater than at least one of these values.

Since the lowest commerce fee is 52000, the condition is satisfied for any student whose fees are greater than 52000.

Result:

Students paying more than 52000 (the smallest commerce fee) qualify.

= ANY is Equivalent to IN

-- These two queries return the same result:
SELECT StudentName FROM Students
WHERE StudentID = ANY (SELECT StudentID FROM Scholarships);

-- is the same as:
SELECT StudentName FROM Students
WHERE StudentID IN (SELECT StudentID FROM Scholarships);

The ALL Operator

ALL compares a value to every value in a set returned by a subquery. The condition is true only if the comparison holds for every value in the set.

Syntax

SELECT column1
FROM table1
WHERE column1 operator ALL (SELECT column FROM table2 WHERE condition);

Example 4: Find Students Whose Fees Are Greater Than ALL Commerce Students' Fees

SELECT StudentName, Course, Fees
FROM Students
WHERE Fees > ALL (
    SELECT Fees FROM Students WHERE Course = 'Commerce'
);

Inner query result (Commerce fees): 52000, 60000

Condition becomes: Fees > 52000 AND Fees > 60000 — i.e., Fees must be greater than the highest commerce fee (60000).

Result: No rows — no student has fees above 60000 (the highest commerce fee). An empty result set is returned.

Example 5: Students With Fees Less Than ALL Science Students

SELECT StudentName, Fees
FROM Students
WHERE Fees < ALL (
    SELECT Fees FROM Students WHERE Course = 'Science'
);

Inner query result (Science fees): 45000, 38000

The condition requires fees to be less than every science fee. The lowest science fee is 38000, so the student must have fees less than 38000.

Result: No students (all fees are at least 38000 or above).

ANY vs ALL Comparison Summary

Key Points to Remember

• EXISTS tests whether a subquery returns any rows — it only cares about presence, not values. Use SELECT 1 in the inner query as a convention.
• NOT EXISTS finds records in the outer table that have no related records in the inner table.
• ANY requires the condition to be true for at least one value in the subquery result.
• ALL requires the condition to be true for every value in the subquery result.
• If the subquery used with ALL returns an empty set, the condition is always true (vacuous truth).
• If the subquery used with ANY returns an empty set, the condition is always false.

Summary

EXISTS checks for the presence of related rows across tables. ANY passes the condition if it holds for at least one value in a set. ALL passes only when the condition holds for every value in the set. Together, these operators allow writing sophisticated subquery-based filters that handle complex multi-table data relationships precisely and efficiently.