ANY Operator in SQL Server

ANY Operator in SQL Server with Examples

In this article, I am going to discuss ANY Operator in SQL Server with examples. Please read our previous article where we discussed SQL Server ALL Operator in detail. When a scalar value has to be compared with a single-column set of values then generally we use IN or JOINs. In addition to that, we can extend the comparison by using the ANY operator.

What is ANY Operator in SQL Server?

The ANY Operator in SQL Server is used to compare a value to each value in a list of results from a query and evaluate to true if the result of an inner query contains at least one row. ANY must match at least one row in the subquery and must be preceded by comparison operators. For example, greater than (>) with ANY means greater than at least one value. If this is not clear at the moment then don’t worry we will try to understand the use of ANY Operator with examples.

Understanding ANY Operator with Examples:

Let us understand the SQL Server ANY Operator with some examples. We are going to use the following PermanentEmployee and ContractEmployee tables to understand ANY Operator.

ANY Operator in SQL Server

Please use the below script to create database EmployeeDB, create PermanentEmployee and ContractEmployee table and populate these two tables with the required data.

-- Create a database
CREATE DATABASE EmployeeDB
GO

Use EmployeeDB
GO

--Create PermanentEmployee Table
CREATE TABLE PermanentEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert Rows into PermanentEmployee Table
INSERT INTO PermanentEmployee VALUES (1,'Pranaya','Male','IT',20)
INSERT INTO PermanentEmployee VALUES (2,'Priyanka','Female','IT',22)
INSERT INTO PermanentEmployee VALUES (3,'Sudhanshu','Male','HR',25)
INSERT INTO PermanentEmployee VALUES (4,'Subrat','Male','Sales',60)
INSERT INTO PermanentEmployee VALUES (5,'Tarun','Male','Sales',54)
INSERT INTO PermanentEmployee VALUES (6,'Lipika','Female','HR',27)
INSERT INTO PermanentEmployee VALUES (7,'Smita','Female','IT',40)
INSERT INTO PermanentEmployee VALUES (8,'Smith','Male','HR',29)
GO
ContractEmployee Table with data:
-- Create ContractEmployee Table
CREATE TABLE ContractEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert rows into ContractEmployee Table
INSERT INTO ContractEmployee VALUES (9,'Anurag','Male','IT',33)
INSERT INTO ContractEmployee VALUES (11,'Sambit','Male','HR',29)
INSERT INTO ContractEmployee VALUES (12,'James','Male','Sales',37)
INSERT INTO ContractEmployee VALUES (13,'Pam','Female','Sales',25)
GO
Syntax to use ANY Operator in SQL Server:

ANY Operator Syntax in SQL Server

Parameters:
  1. column_name: Name of the column of the table.
  2. expression1: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
  3. table_name: Name of the table.
  4. WHERE expression2: Compares a scalar expression until a match is found for ANY operator. One or more rows must match the expression to return a Boolean TRUE value for the ANY operator.
  5. comparison_operator: Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Let’s understand ANY Operator with examples.

Consider a scenario where we have two tables such as PermanentEmployee and ContractEmployee. Both the tables have the column Age. If you need to get all the records from the PermanentEmployee table where the Age is at-least greater than one value from the Age column of ContractEmployee table. What would be your query?

You can use the subquery and MIN function to write your query for the above requirement as shown below.

SELECT * FROM PermanentEmployee WHERE Age > ANY (SELECT MIN(Age) FROM ContractEmployee)

The above query will produce the below result.

understanding ANY Operator with examples

Use ANY Operator to get the required results.

You can also use the ANY operator instead of using Min function with the subquery.  As you want to get all rows from the PermanentEmployee table where Age is greater than any value of Age column in ContractEmployee table, You can use > Any Operator as shown below. Greater than ANY means greater than at least one value that is greater than the minimum.

SELECT * FROM PermanentEmployee WHERE Age > ANY (SELECT Age FROM ContractEmployee)

The above query will produce the same result set as shown below.

ANY Operator in SQL Server

We got the same records that 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 = , <> , != , > , >= , !> , < , <= , !< 

In the next article, I am going to discuss SOME Operator in SQL Server with examples. Here, in this article, I try to explain the ANY Operator in SQL Server step by step with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *