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.
![](../../Content/images/postgre/anyoperator1.png)
![](../../Content/images/postgre/anyoperator2.png)
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') );
![](../../Content/images/postgre/anyoperator3.png)
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'));
![](../../Content/images/postgre/anyoperator4.png)