ANY in SQL

Introduction to ANY in SQL

ANY in Structured Query Language (SQL) is an expression operator generally used in the WHERE or HAVING clause of a SQL INSERT, SELECT, DELETE and UPDATE query, that compares the values of a column to each value obtained from a subquery and evaluates the result for TRUE if the result of anyone row in the result of the subquery satisfies the mentioned condition.

ANY is used primarily to compare the values of a column with values from a subquery. It belongs to the family of other operators like ALL and SOME. ANY expression operator is followed by comparison operators like ‘>’, ‘<’, ‘=’, ‘!=’ etc. It returns TRUE if atleast one value matches.

Syntax

The basic syntax of using ANY in the WHERE clause is as follows :

SELECT column_name(s)
FROM table_name
WHERE test_expression comparison_operator {ANY | ALL | SOME } (SQL subquery);

Parameters

The parameters used in the above syntax are :

• SELECT column_name(s): It is used to select the required data from the database. Mention the column names that you want in the result set.
• FROM table_name: Mention the table name or source from which the columns will be fetched.
• WHERE: It is used to specify the conditions to filter records.
• Test_expression: It is the expression for which we want to perform the test or the column name whose values you want to compare with the values from the subquery.
• Comparison_operator: Mention the comparison operators from the set standard operators like { =, <>, !=, >, >=, <, or <= }.
• { ANY | ALL | SOME }: ANY is the expression operator that returns TRUE if atleast one row satisfies the mentioned condition. ALL is just the opposite of ANY, it returns TRUE only if all the rows satisfies the mentioned condition. SOME is also an expression operator that returns TRUE if more than one row satisfies the mentioned condition.

The above-mentioned parameters, all the parameters are mandatory. You may use GROUP BY, ORDER BY and HAVING clauses based on your requirement. Going ahead we will be discussing the above mentioned ANY operator in great detail. In order to understand the concept better, we will take the help of three tables, employees ( contains personal details of all the employees), department ( contains details like department id, name, and its hod) and tasks (contains details and status of projects).

The data in the “department” table look something like this :

The data in the “employees” table is as follows :

The data in the “tasks” table look something like this :

Examples of ANY in SQL

Following are the example are:

Example #1

Basic sql query to illustrate the use of ANY expression operator.

Query:

SELECT *
FROM employees
WHERE employeeid = ANY (select head from department);

Output:

In the above example, we have tried to obtain the details of any employee who is also the head of department.

Example #2

Use of ANY operator along with an additional WHERE condition.

Find the names of employees who are from ‘Manhattan’ and are also head of a department.

Query:

SELECT lastname,firstname
FROM employees
WHERE city = 'Manhattan' AND employeeid = ANY (select head
from department);

Output:

Example #3

Find the details of the employees who are also head of the department and earn less than 16000.

Query:

SELECT *
FROM employees
WHERE salary < '16000' AND employeeid = ANY (select employeeid
from department);

Output:

In the above example, we were expecting just two rows in the result but since we have used ANY operator, it returns TRUE even if one row satisfies the condition. This illustrates the wrong use of the ANY operator.

Example #4

Use of ANY operator along with GROUP BY and HAVING clause.

Find the count of the employees from each city, ensuring that any of the employees should have completed the projects allotted to them.

Query:

SELECT city, COUNT(employeeid)as 'No of employees'
FROM employees
WHERE employeeid = ANY (SELECT employeeid FROM tasks WHERE status = 'Completed')
GROUP BY city;

Output:

Example #5

Find the count of employees in each department such that the count of any department should be more than the count of employees from one city alone.

Query:

SELECT departmentid, COUNT(employeeid) as 'no of employees'
FROM employees
GROUP BY departmentid
HAVING COUNT(employeeid) > ANY (select count(employeeid) from employees group by city)
ORDER BY COUNT(employeeid) DESC;

Output:

This example might not make much sense to you. But, this example is primarily to illustrate the use of ANY along with GROUP BY, HAVING and ORDER BY clauses.

Example #6

Use of ANY operator along with a JOIN QUERY

Find the count of the employees from each department, ensuring that any of the employees should have completed the projects allotted to them.

Query:

SELECT d.departmentname, COUNT(employeeid)as 'No of employees'
FROM employees as e INNER JOIN department as d
ON e.departmentid = d.departmentid
WHERE employeeid = ANY (SELECT employeeid
FROM tasks WHERE
status = 'Completed')
GROUP BY d.departmentname;

Output:

Recommended Articles

This is a guide to ANY in SQL. Here we discuss the introduction and syntax of any in sql along with different examples and its code implementation. You may also look at the following articles to learn more –

1. EXPLAIN in SQL
2. SQL Variables
3. Array in SQL
4. SQL Bulk Insert