ALL Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

ALL Operator in Oracle with Examples

In this article, I am going to discuss the ALL Operator in Oracle with Examples. Please read our previous article where we discussed ANY Operator in Oracle with Examples. When a scalar value has to be compared with a single-column set of values then generally we use IN or JOINs. In addition to IN or JOINs, we can extend the comparison by using ANY and ALL operators which have rarely been used. At the end of this article, you will understand what is ALL Operator is and when and how to use ALL Operator in Oracle with Examples.

What is ALL Operator in Oracle?

The ALL Operator in Oracle is used to select all records of a Select statement. It compares a value to every value in a list of results from a query. The ALL must be preceded by the comparison operators and evaluated to TRUE if the query returns rows. For example, ALL means greater than every value means greater than the maximum value. Suppose ALL (1, 2, 3) means greater than 3.

The ALL operator in Oracle is used to compare all of the values in the given list. For better understanding, please have a look at the following image.

If this is not clear at the moment don’t worry, we will try to understand this with some examples.

Syntax to use ALL Operator in Oracle:

Parameters:

1. column_name: Name of the column of the table.
2. expression1: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
3. table_name: Name of the table.
4. WHERE expression2: Compares a scalar expression, such as a column against every value in the subquery for ALL Operator. All rows must match the expression to return a Boolean TRUE value for the ALL operator.
5. comparison_operator: Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Understanding ALL Operator in Oracle with Examples:

Let us understand the ALL Operator in Oracle with some examples. We are going to use the following PermanentEmployee and ContractEmployee tables to understand the ALL Operator.

Please use the following SQL script to create PermanentEmployee and ContractEmployee table and populate these two tables with the required sample data.

CREATE TABLE PermanentEmployee
(
  EmployeeId INT,
  Name VARCHAR2(20),
  Gender VARCHAR(10),
  Department varchar(10),
  Age INT
);

INSERT INTO PermanentEmployee VALUES (1,'Pranaya','Male','IT',20);
INSERT INTO PermanentEmployee VALUES (2,'Priyanka','Female','IT',22);
INSERT INTO PermanentEmployee VALUES (3,'Sudhanshu','Male','HR',25);
INSERT INTO PermanentEmployee VALUES (4,'Subrat','Male','Sales',60);
INSERT INTO PermanentEmployee VALUES (5,'Tarun','Male','Sales',54);
INSERT INTO PermanentEmployee VALUES (6,'Lipika','Female','HR',27);
INSERT INTO PermanentEmployee VALUES (7,'Smita','Female','IT',40);
INSERT INTO PermanentEmployee VALUES (8,'Smith','Male','HR',29);

CREATE TABLE ContractEmployee
(
  EmployeeId INT,
  Name VARCHAR(20),
  Gender VARCHAR(10),
  Department varchar(10),
  Age INT
);

INSERT INTO ContractEmployee VALUES (9,'Anurag','Male','IT',33);
INSERT INTO ContractEmployee VALUES (10,'Sara','Female','IT',15);
INSERT INTO ContractEmployee VALUES (11,'Sambit','Male','HR',29);
INSERT INTO ContractEmployee VALUES (12,'James','Male','Sales',37);
INSERT INTO ContractEmployee VALUES (13,'Pam','Female','Sales',25);

All Operator Example:

Here, we have two tables i.e. PermanentEmployee and ContractEmployee, and both the tables have the Age column. If we want to fetch all the records from the PermanentEmployee table where the Age is greater than the maximum value of the Age column in the ContractEmployee table. What would be your query? You can use the subquery and max function to write your query for the above requirement as shown below.

SELECT * FROM PermanentEmployee WHERE Age > (SELECT MAX(AGE) FROM ContractEmployee);

Once you execute the above query, you will get the following output.

For the above requirement, we can also use the ALL operator. In that case, we don’t have to use the Max function. The ALL Operator is going to compare our outer query value to a set of values from the subquery. We can use >All, Greater than ALL means greater than every value returned by the subquery, In other words, greater than max value.

SELECT * FROM PermanentEmployee WHERE Age > ALL (SELECT AGE FROM ContractEmployee);

When you execute the above query, you will get the following output.

With ALL we can use different comparison operators such as =, <>, !=, >, >=, !>, <, <=, !<. The ALL Operator in Oracle can only be used with WHERE and HAVING clauses.

In the next article, I am going to discuss SOME Operator in Oracle with Examples. Here, in this article, I try to explain ALL Operator in Oracle with Examples. I hope you enjoy this article.