SQL ANY Operator

Last update on April 02 2024 10:07:45 (UTC/GMT +8 hours)

ANY Operator

ANY compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. ANY must be preceded by comparison operators. Suppose using greater than ( >) with ANY means greater than at least one value.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

Parameters:

Name	Description
column_name	Name of the column of the table.
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.
table_name	Name of the table.
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.
comparison_operator	Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Pictorical Presentation: SQL ANY Operator

DBMS Support : SQL ANY Operator

DBMS	Command
MySQL	Supported
PostgreSQL	Supported
SQL Server	Supported
Oracle	Supported

Example: SQL ANY operator

Sample table: customer

Sample table: agents

To get 'agent_code', 'agent_name', 'working_area', 'commission' from 'agents' table with following conditions -

1. 'agent_code' should be any 'agent_code' from 'customer' table, which satisfies the condition bellow :

a) 'cust_country' in the 'customer' table must be 'UK',

the following SQL statement can be used :


SELECT agent_code, agent_name, working_area, commission
-- Selecting specific columns: agent_code, agent_name, working_area, and commission
FROM agents
-- From the table named "agents"
WHERE agent_code = ANY (
    SELECT agent_code FROM customer
    WHERE cust_country = 'UK'
);
-- Where the value in the column "agent_code" is equal to any value returned by the subquery
-- Subquery selects agent_code from the customer table where cust_country is 'UK'

Explanation:

SELECT agent_code, agent_name, working_area, commission: This specifies that we want to retrieve specific columns (agent_code, agent_name, working_area, and commission) from the table.

FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".

WHERE agent_code = ANY (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "agent_code" column matches any value returned by the subquery should be included in the result set.

SELECT agent_code FROM customer WHERE cust_country = 'UK': This is a subquery that retrieves the "agent_code" from the "customer" table where the "cust_country" is 'UK'.

ANY (...): This operator is used to check if the left-hand side value matches any of the values returned by the subquery.

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION
---------- -------------------- -------------------- ----------
A009       Benjamin             Hampshair                   .11
A003       Alex                 London                      .13
A006       McDen                London                      .15

SQL ANY operator in nested subquery

In this example, we have discussed how SQL ANY operator can work with nested select statement.

Sample table: customer

Sample table: agents

Sample table: orders

To get 'agent_code', 'agent_name', 'working_area', 'commission' from 'agents' table with following conditions -

1. 'agent_code' should be any 'agent_code' from 'customer' table, which satisfies the condition bellow :
a) 'agent_code' should be any 'agent_code' from 'orders' table, which satisfies the condition bellow :
i) 'advance_amount' of 'orders' table must be more than 600,

the following SQL statement can be used :


SELECT agent_code, agent_name, working_area, commission
-- Selecting specific columns: agent_code, agent_name, working_area, and commission
FROM agents
-- From the table named "agents"
WHERE agent_code = ANY (
    SELECT agent_code FROM customer
    WHERE agent_code = ANY (
        SELECT agent_code FROM orders
        WHERE advance_amount > 600
    )
);
-- Where the value in the column "agent_code" is equal to any value returned by the innermost subquery
-- The innermost subquery selects agent_code from the orders table where advance_amount is greater than 600
-- The middle subquery selects agent_code from the customer table where agent_code is equal to any value returned by the innermost subquery
-- The outer query filters rows from the agents table where agent_code is equal to any value returned by the middle subquery

Explanation:

SELECT agent_code, agent_name, working_area, commission: This specifies that we want to retrieve specific columns (agent_code, agent_name, working_area, and commission) from the table.

FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".

WHERE agent_code = ANY (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "agent_code" column matches any value returned by the subquery should be included in the result set.

SELECT agent_code FROM customer WHERE agent_code = ANY (...): This is a subquery that retrieves the "agent_code" from the "customer" table where the "agent_code" matches any value returned by the innermost subquery.

SELECT agent_code FROM orders WHERE advance_amount > 600: This innermost subquery selects "agent_code" from the "orders" table where the "advance_amount" is greater than 600.

ANY (...): This operator is used to check if the left-hand side value matches any of the values returned by the subquery.

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION
---------- -------------------- -------------------- ----------
A008       Alford               New York                    .12
A010       Santakumar           Chennai                     .14
A005       Anderson             Brisban                     .13
A002       Mukesh               Mumbai                      .11

SQL ANY operator with GROUP BY

In this example we have discussed the usage of SQL ANY operator with SQL GROUP BY in a select statement.

Sample table: customer

Sample table: agents

Sample table: orders

To get 'agent_code', and 'advance_amount' from 'orders' table with following conditions -

1. 'advance_amount' of 'orders' table must be more than 600,
2. 'agent_code' should be any 'agent_code' from 'customer' table, which satisfies the condition bellow :
a) 'agent_code' should be any 'agent_code' from 'agents' table, which satisfies the condition bellow :
i) 'commission' of 'agents' table must be more than or equal to .12,
3. 'agent_code' in 'orders' table must be in a group,

the following SQL statement can be used :


SELECT DISTINCT(agent_code), advance_amount
-- Selecting distinct values of agent_code and advance_amount
FROM orders
-- From the table named "orders"
WHERE advance_amount > 600
-- Where the value in the column "advance_amount" is greater than 600
AND agent_code = ANY (
    SELECT agent_code
    -- Selecting agent_code
    FROM customer
    -- From the table named "customer"
    WHERE agent_code = ANY (
        SELECT agent_code FROM agents
        -- Selecting agent_code from the table named "agents"
        WHERE commission >= .12
        -- Where the value in the column "commission" is greater than or equal to .12
        GROUP BY agent_code
        -- Grouping the results by agent_code
    )
    -- Where agent_code is equal to any value returned by the inner subquery
);
-- Where the value in the column "agent_code" matches any value returned by the innermost subquery

Explanation:

SELECT DISTINCT(agent_code), advance_amount: This specifies that we want to retrieve distinct values of agent_code and advance_amount.

FROM orders: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders".

WHERE advance_amount > 600: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column "advance_amount" is greater than 600 should be included in the result set.

AND agent_code = ANY (...): This is a conditional clause that further filters the rows returned by the query. It specifies that only rows where the value in the column "agent_code" matches any value returned by the subquery should be included in the result set.

The subquery inside ANY (...) retrieves agent_code from the "customer" table where agent_code matches any value returned by the inner subquery.

The inner subquery retrieves agent_code from the "agents" table where commission is greater than or equal to .12, grouping the results by agent_code.

Output:

AGENT_CODE ADVANCE_AMOUNT
---------- --------------
A005                 1800
A008                 2000
A010                  800
A005                  700
A008                 1000
A010                  900
A010                  700

SQL ANY with group by and order by

In this example we have discussed the usage of SQL ANY operator with GROUP BY and ORDER BY clause in a select statement.

Sample table: customer

Sample table: agents

Sample table: orders

To get 'agent_code', and 'advance_amount' from 'orders' table with following conditions -

the following SQL statement can be used :


SELECT DISTINCT(agent_code), advance_amount
-- Selecting distinct values of agent_code and advance_amount
FROM orders
-- From the table named "orders"
WHERE advance_amount > 600
-- Where the value in the column "advance_amount" is greater than 600
AND agent_code = ANY (
    SELECT agent_code
    -- Selecting agent_code
    FROM customer
    -- From the table named "customer"
    WHERE agent_code = ANY (
        SELECT agent_code FROM agents
        -- Selecting agent_code from the table named "agents"
        WHERE commission >= .12
        -- Where the value in the column "commission" is greater than or equal to .12
        GROUP BY agent_code
        -- Grouping the results by agent_code
    )
    -- Where agent_code is equal to any value returned by the inner subquery
)
-- Where the value in the column "agent_code" matches any value returned by the innermost subquery
ORDER BY advance_amount;
-- Ordering the result set by the column "advance_amount"

Explanation:

SELECT DISTINCT(agent_code), advance_amount: This specifies that we want to retrieve distinct values of agent_code and advance_amount.

FROM orders: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders".

WHERE advance_amount > 600: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column "advance_amount" is greater than 600 should be included in the result set.

AND agent_code = ANY (...): This is a conditional clause that further filters the rows returned by the query. It specifies that only rows where the value in the column "agent_code" matches any value returned by the subquery should be included in the result set.

The subquery inside ANY (...) retrieves agent_code from the "customer" table where agent_code matches any value returned by the inner subquery.

The inner subquery retrieves agent_code from the "agents" table where commission is greater than or equal to .12, grouping the results by agent_code.

ORDER BY advance_amount: This orders the result set by the column "advance_amount" in ascending order.

Output:

AGENT_CODE ADVANCE_AMOUNT
---------- --------------
A005                  700
A010                  700
A010                  800
A010                  900
A008                 1000
A005                 1800
A008                 2000

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: BETWEEN
Next: ALL