How to use ANY / SOME Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 127

ANY Logical operator returns TRUE if any one of a set of comparisons are TRUE. ANY compares a scalar value with a single column set of values.

Note :  SOME and ANY are equivalent. We are going to use ANY in our below examples.

Scenario :

Let's say that 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 at-least greater than one value from Age column from dbo.Customer1 table.

Solution:

We can use subquery and MIN 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',10),
(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 min Age value of dbo.Customer1 table by using Subquery and Min function.

Select * From dbo.Customer
where Age> ( Select MIN(age) from dbo.Customer1)




2) Use ANY to get required results.
We can use ANY instead of using Min function with subquery.  As we want to get all rows from dbo.Customer where Age is greater than any value of Age column in dbo.Customer, We will use >Any.
>ANY means greater than at least one value, that is, greater than the minimum.

Select * From dbo.Customer
where Age>ANY ( Select age from dbo.Customer1)



We got the same records what were returned by our first query.
If you will use =ANY that is equal to IN. With ANY you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !< 



Video Demo : How to use ANY / SOME Logical Operator in SQL Server

6 comments:

  1. Thank you so much for sharing your great insight with us!
    Regards: I am a Senior Real Estate Specialist, Who is dedicated to serve Texas senior citizens and disabled communities.

    ReplyDelete
  2. Their Scenario of compares a scalar value with a single column set of values are so reflective explained to all the audiences passing through this blog, which is necessary and creative for the consumers as well as to get the interesting knowledge but also get the most featuring educational Assignment Help Liverpool as well thesedays highly recommended to get into their process which are so impressive as well in whole UK.

    ReplyDelete
    Replies
    1. PaperWriter is a good option for a student on a budget. They are able to write an essay https://studyfy.com/ high quality papers at affordable prices. They have a wide variety of writing services, so you should be able to find what you need. The website also has a bidding system which allows you to place an order for a piece of writing. You can bid on the best price and choose your favorite author.

      Delete
  3. During your paperwriter review https://nocramming.com/paperwriter-review you will come across a number of benefits and drawbacks. It is important to know that the most important aspect of any service is to have a team of competent writers and customer service staff that are available round the clock. These individuals are the backbone of any writing company. They are responsible for taking care of your order from start to finish.

    ReplyDelete
  4. Thank you so much for sharing your great Tutorial with us!
    Regards: Kirkland Hotel

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete