SQL - ANY, SOME Operator

The ANY and SOME operators compare a column value or literal value with the result of a subquery that returns a single-column values. ANY and SOME are the same. You can use any one.

• A subquery used with the ANY or SOME operator, can only return a single column values.
• The ANY or SOME operator must be preceded by comparison operators like =, !=, >, >=, <, <=.
• The ANY or SOME operator uses AND with the result values of a subquery to compare a column of the outer query.
• The data type of the returned values from a subquery must be the same data type as the outer query expression.

Syntax:

expression <operator> ANY( subquery );

expression <operator> SOME( subquery );

-- or    

SELECT * FROM table_name
WHERE column_name <operator> ANY( subquery );

SELECT * FROM table_name
WHERE column_name <operator> SOME( subquery );

Database like SQL Server, PostgreSQL, SQLite supports ANY and SOME operator with subquery whereas Oracle allows subquery as well as literal values with ANY or SOME operator.

For the demo purpose, we will use the following Employee and Department tables in all examples.

ANY, SOME with Expression

The ANY or SOME operator can be used with a scalar expression. For example, the following is used with IF condition:

IF 15000 <=  ANY(SELECT salary FROM Employee)
	print('Some employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

Above, the expression IF 15000 < ANY(SELECT salary FROM Employee) would be transformed using OR operator, as below:

IF 15000 <= 33000 OR 15000 <= 17000 OR 15000 <= 15000 OR 15000 <= 18000 OR 15000 <= 25000
	print('Some employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

So, the above expression would print Some employee's salaries are equal to or more than 15000 in MS SQL Server.

The SOME operator will give the same result.

IF 15000 <=  SOME(SELECT salary FROM Employee)
	print('Some employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

Note that ANY or SOME operator must come after an operator. The following will give syntax error.

IF ANY(SELECT salary FROM Employee) <= 15000 
	print('Some employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

ANY or SOME with Query

The ANY or SOME operator can also be used with a query. The following query uses the ANY or SOME operator with the = operator.

SELECT * FROM Employee 
WHERE DeptId = ANY(
                    SELECT DeptId FROM Department
                    WHERE DeptName = 'HR' OR DeptName = 'Sales'
            );

In the above query, a subquery SELECT DeptId FROM Department WHERE DeptName = 'HR' OR DeptName = 'Sales' would be executed first, and it will return DeptId values 20, 30. Now, the ANY or SOME operator would use OR operator with all the return values and form the query as shown below.

SELECT * FROM Employee 
WHERE DeptId = 20 OR DeptId = 30;

So, the above query will return the records whose DeptId is 20 or 30, as shown below.

Note that the following query with the SOME operator would return the same result as above.

SELECT * FROM Employee 
WHERE DeptId = SOME(
                    SELECT DeptId FROM Department
                    WHERE DeptName = 'HR' OR DeptName = 'Sales'
            );

In the same way, you can use the ANY or SOME operator with <=, <, =>, and > operators.