SQL Server: Tips and Tricks - 2 (2017)

How to use ANY / SOME Logical Operator in SQL Server

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