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.
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.
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
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
--Create dbo.Customer1 table
Create table dbo.Customer1
--Insert rows in dbo.Customer1 Table
insert into dbo.Customer1
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)
With ALL you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<