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)
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)
If you will use =ANY that is equal to IN. With ANY you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !<