SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

ANY and ALL Operators

Learn SQL ANY and ALL Operators in detail, understand how they compare values against multiple rows returned by subqueries, and solve advanced filtering problems.

In SQL, comparison operators such as:

sql example

=
>
<
>=
<=
<>

normally compare one value with another value.

Example:

sql example

SELECT *
FROM Employees
WHERE Salary > 50000;

Here:

text example

Salary
↓
Compared With
↓
Single Value

However, real-world databases often require comparisons against multiple values returned by a subquery.

For example:

Find employees earning more than at least one employee in another department.
Find products priced higher than all products in a category.
Find students scoring better than any student in another class.
Find customers with purchases greater than all purchases made in a region.

To perform these comparisons, SQL provides:

text example

ANY
ALL

operators.

These operators work together with subqueries and allow a value to be compared against multiple rows returned by the subquery.

What is the ALL Operator?

The ALL operator returns TRUE only if the comparison is TRUE for every value returned by the subquery.

Think of ALL as:

text example

Every Comparison Must Match

If even one comparison fails:

text example

Result = FALSE

Understanding ANY

Suppose a subquery returns:

Output

20000
30000
40000

Query:

sql example

Salary > ANY
(
    20000,
    30000,
    40000
)

Evaluation:

Output

Salary > 20000 ?
OR
Salary > 30000 ?
OR
Salary > 40000 ?

If any one condition is TRUE:

text example

Result = TRUE

Understanding ALL

Suppose a subquery returns:

Output

20000
30000
40000

Query:

sql example

Salary > ALL
(
    20000,
    30000,
    40000
)

Evaluation:

Output

Salary > 20000 ?
AND
Salary > 30000 ?
AND
Salary > 40000 ?

Every comparison must be TRUE.

Basic ANY Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE value operator ANY
(
    subquery
);

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ANY
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Basic ALL Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE value operator ALL
(
    subquery
);

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ALL
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Creating Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

Insert records:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 1, 50000),
(2, 'Priya', 1, 70000),
(3, 'Amit', 2, 60000),
(4, 'Neha', 2, 80000);

ANY Operator Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ANY
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Subquery returns:

Output

60000
80000

Evaluation:

For Rahul:

text example

50000 > 60000 → False
50000 > 80000 → False

Rejected.

For Priya:

text example

70000 > 60000 → True
70000 > 80000 → False

At least one TRUE.

Result:

text example

Priya

ALL Operator Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ALL
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Subquery returns:

Output

60000
80000

Evaluation:

For Priya:

text example

70000 > 60000 → True
70000 > 80000 → False

Rejected.

For Neha:

text example

80000 > 60000 → True
80000 > 80000 → False

Rejected.

No rows returned.

Understanding the Difference

Subquery Values:

Output

100
200
300

Condition:

sql example

500 > ANY

Result:

Output

TRUE

because:

text example

500 > 100

Condition:

sql example

500 > ALL

Result:

Output

TRUE

because:

Output

500 > 100
500 > 200
500 > 300

all are true.

ANY with Greater Than (>)

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price > ANY
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 2
);

Meaning:

Output

More Expensive Than
At Least One Product
In Category 2

ALL with Greater Than (>)

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price > ALL
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 2
);

Meaning:

Output

More Expensive Than
Every Product
In Category 2

ANY with Less Than (<)

Example:

sql example

SELECT StudentName
FROM Students
WHERE Marks < ANY
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 1
);

Meaning:

Output

Less Than At Least One Student
In Class 1

ALL with Less Than (<)

Example:

sql example

SELECT StudentName
FROM Students
WHERE Marks < ALL
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 1
);

Meaning:

Output

Less Than Every Student
In Class 1

Real-World Example: E-Commerce

Requirement:

text example

Products More Expensive
Than Every Product
In Category A

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price > ALL
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 1
);

Real-World Example: Education System

Requirement:

Output

Students Scoring Better
Than At Least One Student
In Another Class

Query:

sql example

SELECT StudentName
FROM Students
WHERE Marks > ANY
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 2
);

Real-World Example: Banking

Requirement:

Output

Customers With Balance
Greater Than Every Customer
In Branch B

Query:

sql example

SELECT CustomerName
FROM Accounts
WHERE Balance > ALL
(
    SELECT Balance
    FROM Accounts
    WHERE BranchID = 2
);

ANY vs IN

Many beginners confuse ANY and IN.

Example:

sql example

WHERE DepartmentID IN
(
    1,2,3
)

Equivalent to:

sql example

WHERE DepartmentID =
ANY
(
    1,2,3
)

For equality comparisons:

Output

IN
≈
= ANY

ANY vs ALL Comparison

Feature	ANY	ALL
Requires One Match	Yes	No
Requires Every Match	No	Yes
Flexible Condition	Easier	Stricter
Result Set Size	Larger	Smaller

Performance Considerations

Performance depends on:

Subquery size
Indexes
Comparison operator
Database engine

Large subqueries may require optimization.

Indexes on comparison columns significantly improve performance.

Common Errors

Empty Subquery Results

Example:

sql example

Salary > ALL
(
    Empty Result
)

Behavior depends on the database system.

Always test edge cases.

Confusing ANY and ALL

ANY:

Output

One Match Needed

ALL:

text example

Every Match Needed

Using Incorrect Operators

Example:

sql example

= ALL

often produces unexpected results.

Ignoring NULL Values

Subquery results containing NULL may affect comparisons.

Best Practices

Understand Business Requirements

Determine whether:

Output

One Match

text example

Every Match

is required.

Test Subqueries Independently

Run the subquery first.

Use Proper Indexing

Improves performance.

Handle NULL Values Carefully

Avoid unexpected comparison behavior.

Use Meaningful Aliases

Makes complex queries easier to understand.

Common Interview Questions

What does ANY do?

Returns TRUE if the condition matches at least one value from the subquery.

What does ALL do?

Returns TRUE only if the condition matches every value from the subquery.

Which operator is more restrictive?

sql example

ALL

because every comparison must be TRUE.

Is IN similar to ANY?

Yes.

sql example

IN

is similar to:

sql example

= ANY

Can ANY and ALL be used without subqueries?

They are primarily designed for use with subqueries.

Summary

ANY and ALL are powerful SQL operators used for comparing a value against multiple rows returned by a subquery. They enable advanced filtering and help solve complex business problems involving group-based comparisons.

In this lesson, you learned:

What ANY is
What ALL is
How ANY works
How ALL works
ANY syntax
ALL syntax
Real-world examples
ANY vs IN
ANY vs ALL
Performance considerations
Best practices

Mastering ANY and ALL helps you write advanced SQL queries that compare values against entire sets of data efficiently.

Next Step

Continue to the next lesson:

Scalar Subqueries →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for ANY and ALL Operators.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, any, all

Continue learning this concept

SubqueriesCASE Expression in SQLLearn how to use the CASE expression in SQL for conditional logic within queries, understand simple and searched CASE syntax, and apply it in real-world scenarios.SubqueriesCorrelated SubqueryLearn SQL Correlated Subqueries in detail, understand row-by-row execution, how they differ from normal subqueries, and solve advanced business problems efficiently.SubqueriesEXISTS OperatorLearn SQL EXISTS Operator in detail, understand how it checks for the existence of rows in a subquery, improves query efficiency, and is used in real-world database applications.SubqueriesNested Subqueries in SQLLearn how to write nested subqueries (subqueries within subqueries) in SQL, understand multiple levels of nesting, and solve complex data retrieval problems.SubqueriesScalar SubqueriesLearn SQL Scalar Subqueries in detail, understand how they return a single value, how they are used in SELECT, WHERE, HAVING, and ORDER BY clauses, and solve advanced SQL problems efficiently.SubqueriesSubquery IntroductionLearn SQL Subqueries in detail, understand how queries can be nested inside other queries, and discover real-world use cases for filtering, comparisons, and advanced data retrieval.

PreviousSQL Joins Overview NextCASE Expression in SQL

SQL Notes

ANY and ALL Operators

Learn SQL ANY and ALL Operators in detail, understand how they compare values against multiple rows returned by subqueries, and solve advanced filtering problems.

In SQL, comparison operators such as:

sql example

=
>
<
>=
<=
<>

normally compare one value with another value.

Example:

sql example

SELECT *
FROM Employees
WHERE Salary > 50000;

Here:

text example

Salary
↓
Compared With
↓
Single Value

However, real-world databases often require comparisons against multiple values returned by a subquery.

For example:

Find employees earning more than at least one employee in another department.
Find products priced higher than all products in a category.
Find students scoring better than any student in another class.
Find customers with purchases greater than all purchases made in a region.

To perform these comparisons, SQL provides:

text example

ANY
ALL

operators.

These operators work together with subqueries and allow a value to be compared against multiple rows returned by the subquery.

What is the ALL Operator?

The ALL operator returns TRUE only if the comparison is TRUE for every value returned by the subquery.

Think of ALL as:

text example

Every Comparison Must Match

If even one comparison fails:

text example

Result = FALSE

Understanding ANY

Suppose a subquery returns:

Output

20000
30000
40000

Query:

sql example

Salary > ANY
(
    20000,
    30000,
    40000
)

Evaluation:

Output

Salary > 20000 ?
OR
Salary > 30000 ?
OR
Salary > 40000 ?

If any one condition is TRUE:

text example

Result = TRUE

Understanding ALL

Suppose a subquery returns:

Output

20000
30000
40000

Query:

sql example

Salary > ALL
(
    20000,
    30000,
    40000
)

Evaluation:

Output

Salary > 20000 ?
AND
Salary > 30000 ?
AND
Salary > 40000 ?

Every comparison must be TRUE.

Basic ANY Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE value operator ANY
(
    subquery
);

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ANY
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Basic ALL Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE value operator ALL
(
    subquery
);

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ALL
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Creating Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

Insert records:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 1, 50000),
(2, 'Priya', 1, 70000),
(3, 'Amit', 2, 60000),
(4, 'Neha', 2, 80000);

ANY Operator Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ANY
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Subquery returns:

Output

60000
80000

Evaluation:

For Rahul:

text example

50000 > 60000 → False
50000 > 80000 → False

Rejected.

For Priya:

text example

70000 > 60000 → True
70000 > 80000 → False

At least one TRUE.

Result:

text example

Priya

ALL Operator Example

Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > ALL
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentID = 2
);

Subquery returns:

Output

60000
80000

Evaluation:

For Priya:

text example

70000 > 60000 → True
70000 > 80000 → False

Rejected.

For Neha:

text example

80000 > 60000 → True
80000 > 80000 → False

Rejected.

No rows returned.

Understanding the Difference

Subquery Values:

Output

100
200
300

Condition:

sql example

500 > ANY

Result:

Output

TRUE

because:

text example

500 > 100

Condition:

sql example

500 > ALL

Result:

Output

TRUE

because:

Output

500 > 100
500 > 200
500 > 300

all are true.

ANY with Greater Than (>)

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price > ANY
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 2
);

Meaning:

Output

More Expensive Than
At Least One Product
In Category 2

ALL with Greater Than (>)

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price > ALL
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 2
);

Meaning:

Output

More Expensive Than
Every Product
In Category 2

ANY with Less Than (<)

Example:

sql example

SELECT StudentName
FROM Students
WHERE Marks < ANY
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 1
);

Meaning:

Output

Less Than At Least One Student
In Class 1

ALL with Less Than (<)

Example:

sql example

SELECT StudentName
FROM Students
WHERE Marks < ALL
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 1
);

Meaning:

Output

Less Than Every Student
In Class 1

Real-World Example: E-Commerce

Requirement:

text example

Products More Expensive
Than Every Product
In Category A

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price > ALL
(
    SELECT Price
    FROM Products
    WHERE CategoryID = 1
);

Real-World Example: Education System

Requirement:

Output

Students Scoring Better
Than At Least One Student
In Another Class

Query:

sql example

SELECT StudentName
FROM Students
WHERE Marks > ANY
(
    SELECT Marks
    FROM Students
    WHERE ClassID = 2
);

Real-World Example: Banking

Requirement:

Output

Customers With Balance
Greater Than Every Customer
In Branch B

Query:

sql example

SELECT CustomerName
FROM Accounts
WHERE Balance > ALL
(
    SELECT Balance
    FROM Accounts
    WHERE BranchID = 2
);

ANY vs IN

Many beginners confuse ANY and IN.

Example:

sql example

WHERE DepartmentID IN
(
    1,2,3
)

Equivalent to:

sql example

WHERE DepartmentID =
ANY
(
    1,2,3
)

For equality comparisons:

Output

IN
≈
= ANY

ANY vs ALL Comparison

Feature	ANY	ALL
Requires One Match	Yes	No
Requires Every Match	No	Yes
Flexible Condition	Easier	Stricter
Result Set Size	Larger	Smaller

Performance Considerations

Performance depends on:

Subquery size
Indexes
Comparison operator
Database engine

Large subqueries may require optimization.

Indexes on comparison columns significantly improve performance.

Common Errors

Empty Subquery Results

Example:

sql example

Salary > ALL
(
    Empty Result
)

Behavior depends on the database system.

Always test edge cases.

Confusing ANY and ALL

ANY:

Output

One Match Needed

ALL:

text example

Every Match Needed

Using Incorrect Operators

Example:

sql example

= ALL

often produces unexpected results.

Ignoring NULL Values

Subquery results containing NULL may affect comparisons.

Best Practices

Understand Business Requirements

Determine whether:

Output

One Match

text example

Every Match

is required.

Test Subqueries Independently

Run the subquery first.

Use Proper Indexing

Improves performance.

Handle NULL Values Carefully

Avoid unexpected comparison behavior.

Use Meaningful Aliases

Makes complex queries easier to understand.

Common Interview Questions

What does ANY do?

Returns TRUE if the condition matches at least one value from the subquery.

What does ALL do?

Returns TRUE only if the condition matches every value from the subquery.

Which operator is more restrictive?

sql example

ALL

because every comparison must be TRUE.

Is IN similar to ANY?

Yes.

sql example

IN

is similar to:

sql example

= ANY

Can ANY and ALL be used without subqueries?

They are primarily designed for use with subqueries.

Summary

In this lesson, you learned:

What ANY is
What ALL is
How ANY works
How ALL works
ANY syntax
ALL syntax
Real-world examples
ANY vs IN
ANY vs ALL
Performance considerations
Best practices

Mastering ANY and ALL helps you write advanced SQL queries that compare values against entire sets of data efficiently.

Next Step

Continue to the next lesson:

Scalar Subqueries →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for ANY and ALL Operators.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, any, all

Continue learning this concept

PreviousSQL Joins Overview NextCASE Expression in SQL