PostgreSQL: ANY/SOME Operator
PostgreSQL supports ANY and SOME operators that compare a column value or literal value with the result of a subquery that returns a single-column value.
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.
ANY( subquery ); --or expression SOME( subquery ); -- or SELECT * FROM <table_name> WHERE <column_name> <operator> ANY( subquery ); SELECT * FROM table_name WHERE <column_name> <operator> SOME( subquery )
Let's use the following
Department (parent table) and
Employee (child table) to demonstrate the ANY operator.
Let's find out list of
Employees who belongs to
'IT' department. We can do so by running below subquery with ANY operator.
SELECT * FROM Employee WHERE dept_id = ANY (SELECT dept_id FROM Department WHERE dept_name IN ('HR', 'IT') );
In the above query, a subquery
SELECT dept_id FROM Department WHERE dept_name IN ('HR', 'IT') will be executed first, and it will return dept_id values 1,2. 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 dept_id = 1 OR dept_id = 2;
When you use the
= operator with ANY, it works same as the
Note that the following query with the SOME operator would return the same result as above.
SELECT * FROM Employee WHERE dept_id = SOME (SELECT dept_id FROM Department WHERE dept_name IN ('HR', 'IT'));