PostgreSQL ANY Operator

PostgreSQL ANY Operator

The ANY operator in PostgreSQL is used to compare a value with any value returned by a subquery or array.
The condition is TRUE if it matches at least one value.

🔹 Think of ANY as “OR logic” over multiple values.

1️⃣ Basic ANY Syntax

With a subquery

expression operator ANY (subquery);

With an array

expression operator ANY (ARRAY[value1, value2, value3]);

2️⃣ Simple ANY Example (Subquery)

Find products whose price is greater than at least one product in category mobile.



 

✔ Returns products priced higher than the cheapest mobile.

3️⃣ ANY with = (Equal)





 

✔ Same as:



 

4️⃣ ANY with Comparison Operators

OperatorMeaning with ANY
= ANYEqual to at least one
> ANYGreater than minimum
< ANYLess than maximum
>= ANY≥ smallest value
<= ANY≤ largest value
!= ANYNot equal to at least one

Example:

SELECT *
FROM employees
WHERE salary >= ANY (
SELECT salary FROM employees WHERE department = 'HR'
);

5️⃣ ANY vs ALL

-- ANY: at least one match
WHERE salary > ANY (subquery);— ALL: must satisfy condition for all values
WHERE salary > ALL (subquery);

Example:

SELECT *
FROM employees
WHERE salary > ALL (
SELECT salary FROM employees WHERE department = 'HR'
);

✔ Greater than highest HR salary.

6️⃣ ANY with EXISTS (Conceptual)

ANYEXISTS
Compares valuesChecks existence
Returns TRUE if one matchReturns TRUE if any row exists

7️⃣ ANY with DELETE

DELETE FROM users
WHERE id = ANY (
SELECT user_id FROM banned_users
);

8️⃣ Common Mistakes

❌ Using ANY with empty subquery
✔ Returns FALSE

❌ Confusing ANY with ALL
✔ Remember: ANY = OR, ALL = AND

🔟 Best Practices

✔ Use IN for simple equality checks
✔ Use ANY for complex comparisons
✔ Index subquery columns
✔ Prefer EXISTS for pure existence checks

Quick Summary

= ANY → same as IN
> ANY → greater than minimum
< ANY → less than maximum

You may also like...