ALL Operator

ALL Operator in SQL Server

In this article, I will discuss the use of ALL operator in SQL Server

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 that, we can extend the comparison by using ANY and ALL operators which have rarely been used.

ALL is used to select all records of a Select. 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 evaluates to TRUE if the query returns no rows. For example, ALL means greater than every value means greater than the maximum value. Suppose ALL (1, 2, 3) means greater than 3.

Use below script to create database EmployeeDB, create PermanentEmployee and ContractEmployee table and populate these two tables with some test data.

-- Create a databale
CREATE DATABASE EmployeeDB
GO

Use EmployeeDB
GO

--Create PermanentEmployee Table
CREATE TABLE PermanentEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert Rows into PermanentEmployee Table
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)
GO
Fetch the records:

SELECT * FROM PermanentEmployee

ALL Operator in SQL Server

-- Create ContractEmployee Table
CREATE TABLE ContractEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert rows into ContractEmployee Table
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)
GO
Fetch the records:

SELECT * FROM ContractEmployee

ALL Operator in SQL Server

ALL is used with WHERE and HAVING statement.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} (subquery)
Parameters:

column_name: Name of the column of the table.

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.

table_name: Name of the table.

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.

comparison_operator: Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Let’s understand ALL with examples.

Scenario:

Consider a scenario where we have two tables such as PermanentEmployee and ContractEmployee. Both the tables have the column Age. If we need to get all the records from PermanentEmployee table where the Age is greater than the maximum value of Age column in ContractEmployee table. What would be your query?

Solution:

We can use subquery and max function to write our query for above requirement as shown below.

SELECT * FROM PermanentEmployee

WHERE Age > (SELECT MAX(AGE) FROM ContractEmployee)

The above query will produce the following result.

ALL Operator in SQL Server

Let’s see how to use All Operator to get the same result.

For the above requirement, we can also use ALL logical operator. In that case, we don’t have to use Max function. ALL 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)

The above query will produce the same result as shown below.

ALL Operator in SQL Server

With ALL we can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<

Let’s consider ALL operator with one more example.

Create a temporary table and populate the temporary table with some test data as shown below.

CREATE TABLE #TEMP_TABLE (ID INT)
 
INSERT INTO #TEMP_TABLE VALUES(1)
INSERT INTO #TEMP_TABLE VALUES(2)
INSERT INTO #TEMP_TABLE VALUES(3)

Example1:

IF 4 > ALL (SELECT ID FROM #TEMP_TABLE)

PRINT ‘Returned True’

ELSE

PRINT ‘Returned False’

In the above query 4 is greater than all of 1, 2 and 3. So 4, when compared with all of 1, 2 and 3 we can find 4, is greater. Hence the result is true.

Exampel2:

IF 3 > ALL (SELECT ID FROM #TEMP_TABLE)

PRINT ‘Returned True’

ELSE

PRINT ‘Returned False’

In the above query 3, greater is than all of 1, 2 and 3. So 3, when compared with all of 1, 2 and 3 we can find 3, is greater than 1 and 2 but not greater than 3. So 3 is not greater than all of 1, 2 and 3. Hence the result is false.

In the next article, I will discuss ANY Operator in SQL Server.

SUMMARY

In this article, I try to explain the ALL Operator in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *