How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 126

ALL Logical operator returns TRUE if all of a set of comparisons are TRUE. ALL compares a scalar value with a single column set of values.

Let's understand ALL with examples.

Scenario :

Think about a scenario where we have two tables dbo.Customer and dbo.Customer1. Both tables has the column Age. If you need to get all the records from dbo.Customer table where Age is greater than maximum value of Age column in dbo.Customer1 table.What would be your query.

Solution:

We can use subquery and max function to write our query for above requirement. Let's create the tables first.

--Create Customer Table
Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87)

--Create dbo.Customer1 table
Create table dbo.Customer1
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
Values
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)


1) Get all the records from dbo.Customer table where Age is greater than maximum Age value of dbo.Customer1 table by using Subquery and Max function.


2) using ALL with SubQuery
For above requirement we can use ALL logical operator. In that case we don't have to use Max function. ALL is going compare our outer query value to set of values from subquery. We can use >All,  >ALL means greater than every value returned by subquery, In other words greater than max value.

Select * From dbo.Customer
where Age> All ( Select age from dbo.Customer1)
How to use ALL Logical Operator in SQL Server - SQL Server / TSQL Tutorial



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