SQL

ALL in SQL

🌐 The ALL operator in SQL is used to compare a value with every value returned by a subquery. A condition using ALL evaluates to TRUE only if the comparison is true for all rows returned by the subquery. It is commonly used with comparison operators such as >, <, >=, <=, =, and <>.

📖 What is the ALL Operator?

The ALL operator compares a value from the outer query with every value returned by a subquery. The condition succeeds only if the comparison is true for every value in the subquery result.

Information

The ALL operator must always be used together with a comparison operator and a subquery.

📝 Basic Syntax

General Syntax

SELECT column_name
FROM table_name
WHERE column_name comparison_operator ALL (
    SELECT column_name
    FROM another_table
);

📊 Sample Tables

Consider the following Students table:

StudentID	Name	Department	Marks
101	Alice	Computer Science	92
102	Bob	Mathematics	85
103	Charlie	Physics	78
104	David	Computer Science	95
105	Eva	Mathematics	88

Consider the following ScholarshipStudents table:

StudentID	Marks
201	80
202	82
203	84

🎯 Basic ALL Example

Retrieve students whose marks are greater than every scholarship student's marks.

Using > ALL

SELECT *
FROM Students
WHERE Marks > ALL (
    SELECT Marks
    FROM ScholarshipStudents
);

Since the highest scholarship mark is 84, only students with marks greater than 84 are returned.

⚖️ Using Different Comparison Operators

The ALL operator can be combined with various comparison operators.

Operator	Meaning
> ALL	Greater than every returned value.
< ALL	Less than every returned value.
>= ALL	Greater than or equal to every returned value.
<= ALL	Less than or equal to every returned value.
= ALL	Equal to every returned value.
<> ALL	Not equal to every returned value.

🔽 Using < ALL

Find students whose marks are less than every scholarship student's marks.

Using < ALL

SELECT *
FROM Students
WHERE Marks < ALL (
    SELECT Marks
    FROM ScholarshipStudents
);

This query returns students whose marks are below the lowest scholarship student's marks.

🔍 ALL with Aggregate Logic

The ALL operator is useful for comparing values against an entire result set without explicitly calculating the minimum or maximum value.

Highest Marks Comparison

SELECT Name,
       Marks
FROM Students
WHERE Marks >= ALL (
    SELECT Marks
    FROM ScholarshipStudents
);

📊 ALL vs ANY

Although both operators compare values returned by a subquery, they behave differently.

Feature	ALL	ANY
Condition	Must be true for every returned value.	Must be true for at least one returned value.
Strictness	More restrictive.	Less restrictive.
Typical Usage	Compare against the entire result set.	Compare against one or more values.

📊 ALL vs MAX() and MIN()

In some cases, ALL can produce results similar to using aggregate functions, although the syntax differs.

Expression	Equivalent Logic
Marks > ALL (...)	Greater than the maximum returned value.
Marks < ALL (...)	Less than the minimum returned value.

💼 Real-World Example

A university wants to identify students whose marks are higher than every scholarship applicant's marks.

Real-World Query

SELECT StudentID,
       Name,
       Marks
FROM Students
WHERE Marks > ALL (
    SELECT Marks
    FROM ScholarshipStudents
)
ORDER BY Marks DESC;

This query lists students whose performance exceeds that of every scholarship student.

⚠️ Best Practices

Best Practice

Use ALL when a comparison must succeed against every value returned by a subquery. Ensure the subquery returns compatible data types, understand how empty subquery results affect the comparison in your database system, and choose ALL instead of aggregate functions only when it improves readability.

🚀 Key Points to Remember

📌 ALL compares a value with every value returned by a subquery.
📌 The condition is true only if every comparison succeeds.
📌 ALL must be used with a comparison operator.
📌 It is more restrictive than ANY.
📌 > ALL behaves similarly to comparing with the maximum value.
📌 < ALL behaves similarly to comparing with the minimum value.

>>"The ALL operator ensures that every value returned by a subquery satisfies the comparison before a row is selected."

Summary

✅ The ALL operator is a powerful SQL feature for comparing a value against an entire set of results returned by a subquery. By requiring every comparison to succeed, it enables strict filtering and is especially useful for advanced data analysis and decision-making queries.