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 = , <> , != , > , >= , !> , < , <= , !<