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.
EmpId | FirstName | LastName | Salary | DeptId | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | 33000 | 10 |
2 | 'James' | 'Bond' | 10 | ||
3 | 'Neena' | 'Kochhar' | '[email protected]' | 17000 | 20 |
4 | 'Lex' | 'De Haan' | '[email protected]' | 15000 | 10 |
5 | 'Amit' | 'Patel' | 18000 | 30 | |
6 | 'Abdul' | 'Kalam' | '[email protected]' | 25000 | 20 |
DeptId | DeptName |
---|---|
10 | 'Finance' |
20 | 'HR' |
30 | 'Sales' |
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.
EmpId | FirstName | LastName | Salary | DeptId | |
---|---|---|---|---|---|
3 | 'Neena' | 'Kochhar' | '[email protected]' | 17000 | 20 |
5 | 'Amit' | 'Patel' | 18000 | 30 | |
6 | 'Abdul' | 'Kalam' | '[email protected]' | 25000 | 20 |
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.