How to use ALL Logical Operator in SQL Server - SQL Server: Tips and Tricks - 2 (2017)

SQL Server: Tips and Tricks - 2 (2017)

How to use ALL Logical Operator in SQL Server

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.

https://1.bp.blogspot.com/-efFo5xMSh4c/V2qlEkSxgVI/AAAAAAAAfSs/Rcjqi4CrWIMbh679moRRzE29WZ49y-CqwCLcB/s640/Capture.PNG



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)

https://3.bp.blogspot.com/-efFo5xMSh4c/V2qlEkSxgVI/AAAAAAAAfS0/9utiTDjP91Qs0igS3NgiLvOI-3IkrfEmQCKgB/s640/Capture.PNG

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