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


Video Demo : How to use ALL Logical Operator in SQL / TSQL 

5 comments:

  1. Thank you for sharing an amazing and wonderful blog. This content is very useful, informative, and valuable for expanding your knowledge. Keep sharing this type of content with us and keep updating us on new blogs. I like to learn new things. I am interested in game development. I work in the firm Stepico. I recommend visiting the site.

    ReplyDelete
  2. Scenario, idea, concept design are of great importance in game development. Moreover, the difficulty is that this is not a technical component, so in this case it is better to work with really real experts, for example, https://ilogos.biz/2d-game-art-outsoursing/. If you need to create a unique concept art for the game, then I especially recommend ilogos specialists.

    ReplyDelete
  3. Thank you so much for sharing. I have found it extremely helpful! Furthermore, We are the top cleaning company in Atlanta that offers cleaning services for homes, apartments, and offices. Contact us at: https://bakershomecleaning.com

    ReplyDelete
  4. This article contains a wealth of information that is both practical and insightful.
    Regards,
    Birds Oasis is dedicated to provide high-quality Psittacus breeding birds Products, maintenance food, hand feeding & health care products in Pakistan.

    ReplyDelete
  5. "Crafted with precision, this elegant suit features exquisite ostrich fur stitching on the jacket and skirt, showcasing a luxurious and distinctive style."
    Ostrich fur stitching suit jacket skirt

    ReplyDelete