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.

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 'HR' or '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 IN operator.

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'));