Open In App

PL/SQL ALL, ANY Operator

Last Updated : 10 Sep, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

The ALL and ANY operators in PL/SQL are powerful tools used to compare a given value against a set of values returned by a subquery. These operators allow for more dynamic and flexible queries by evaluating conditions against multiple results.

The ALL operator checks if a condition holds true for every value in the result set while the ANY operator checks if the condition is true for at least one value in the set. In this article, We will learn about PL/SQL ALL, ANY Operator by understanding various examples and so on.

PL/SQL ALL Operator

The ALL operator is used to compare a value to the all values returned by the subquery. It evaluates the comparison condition against every value produced by the subquery. The comparison returns TRUE if the condition holds true for the every value in the result set.

Syntax:

expression operator ALL (subquery)

  • expression: The value being compared.
  • operator: The comparison operator (=, >, <, >=, <=, <>).
  • subquery: A query that returns a set of the values.

PL/SQL ANY Operator

The ANY operator compares a value with the any value returned by a subquery. It returns TRUE if the condition is true for at least one value in result set.

Syntax:

expression operator ANY (subquery)

  • expression: The value being compared.
  • operator: The comparison operator (=, >, <, >=, <=, <>).
  • subquery: A query that returns a set of the values.

Examples of PL/SQL ALL, ANY Operator

Consider a table named employees with the following structure and data:

employee_id

Name

salary

department

1

John Doe

50000

HR

2

Jane Smith

70000

IT

3

Sam Brown

60000

IT

4

Lisa White

55000

HR

5

Dave Clark

75000

IT

Examples Using ALL Operator

Example 1: Find Employees with a Salary Greater than All IT Employees

SELECT Name, Salary
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'IT');

output:

Empty

The No employee in the Employees table has a salary greater than all IT employees' salaries (60000, 70000, 75000). Therefore, the query returns no rows.

Example 2: Find Employees with a Salary Less than All IT Employees

SELECT Name, Salary
FROM Employees
WHERE Salary < ALL (SELECT Salary FROM Employees WHERE Department = 'IT');

output:

Examples-Using--ALL-Operator1
Examples-Using--ALL-Operator1

"John Doe" and "Lisa White" have salaries less than the lowest IT salary (60000) so they are included in the result.

Examples Using ANY Operator

Example 3: Find Employees with a Salary Greater than Any IT Employee

SELECT Name, Salary
FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'IT');

output:

Examples-Using-ANY-Operator3
Examples-Using-ANY-Operator3

"Jane Smith" and "Dave Clark" have salaries greater than at least one of the IT salaries (60000) so they are included in the result.

Example 4: Find Employees with a Salary Less than Any IT Employee

SELECT Name, Salary
FROM Employees
WHERE Salary < ALL (SELECT Salary FROM Employees WHERE Department = 'IT');

output:

Examples-Using-ANY-Operator4

"John Doe" and "Lisa White" have salaries less than at least one of the IT salaries (75000).

Combining ALL and ANY with Other Operators

Combining ALL and ANY with the other operators can create more complex conditions.

Example 1: Find HR Employees with a Salary Less than All IT Employees and More than 50000

SELECT Name, Salary
FROM Employees
WHERE Department = 'HR' AND Salary < ALL (SELECT Salary FROM Employees WHERE Department = 'IT') AND Salary > 50000;

output:

Combining-ALL-and-ANY-with-Other-Operators1

"Lisa White" is the only HR employee with a salary less than all IT employees' salaries and greater than 50000.

Example 2: Find Employees with a Salary Greater than 60000 and Less than Any IT Employee

SELECT Name, Salary
FROM Employees
WHERE Salary > 60000 AND Salary < ANY (SELECT Salary FROM Employees WHERE Department = 'IT');

output:

Combining-ALL-and-ANY-with-Other-Operators2

"Jane Smith" has a salary greater than 60000 and less than the highest IT salary (75000), so she is included in the result.

Conclusion

The ALL and ANY operators in PL/SQL provide the powerful tools for performing the complex comparisons in the SQL queries. By using these operators we can compare a value against multiple values returned by the subquery enhancing the ability to query and analyze data effectively.


Next Article
Article Tags :

Similar Reads