dots Created with Sketch.
Yogesh Chauhan's Blog

Everything you need to know about ANY, SOME and ALL Operators in Postgres

in Postgres on January 8, 2021

ANY/SOME

Syntax:


expression operator ANY (subquery)
expression operator SOME (subquery)

The ANY/SOME operator compares a value to a set of values returned by a subquery.

The right-hand side is a parenthesized subquery, which must return exactly one column.

The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result.

The result of ANY is “true” if any true result is obtained. The result is “false” if no true result is found (including the special case where the subquery returns no rows).

SOME is a synonym for ANY. IN is equivalent to = ANY.

If there are no successes and at least one right-hand row yields null for the operator’s result, the result of the ANY construct will be null, not false.

We can use any of these operators:

Operator Description
< less than
> greater than
<= less than or equal to
>= greater than or equal to
= equal
<> or != not equal

Detailed Syntax:


SELECT column(s)
FROM table
WHERE column operator ANY
(SELECT column FROM table WHERE condition);

Postgres ANY Operator Example

I am using the database for all examples. It is available on my Github public repo


SELECT product_name
FROM products
WHERE product_id = ANY (SELECT product_id FROM order_details 
						WHERE quantity = 10);

//Output
product_name
"Chai"
"Chang"
"Chef Anton's Cajun Seasoning"
"Uncle Bob's Organic Dried Pears"
...
...
60 rows

Another Postgres ANY Operator Example:


SELECT product_name
FROM products
WHERE product_id = ANY (SELECT product_id FROM order_details 
						WHERE quantity > 99);

//Output
product_name
"Chai"
"Chang"
"Chef Anton's Cajun Seasoning"
"Uncle Bob's Organic Dried Pears"
...
...
20 rows

As, I mentioned earlier, IN is equivalent to = ANY.

So, if we write down above query using IN, we’ll get the same results.

Postgres IN Operator Example:


SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM order_details 
						WHERE quantity > 99);

//Output
product_name
"Chai"
"Chang"
"Chef Anton's Cajun Seasoning"
"Uncle Bob's Organic Dried Pears"
...
...
20 rows

<> ANY operator is different from NOT IN

Postgres <> ANY Operator Example:


SELECT product_name
FROM products
WHERE product_id <> ANY (SELECT product_id FROM order_details 
						WHERE quantity > 99);

//Output
product_name
"Chai"
"Chang"
"Aniseed Syrup"
...
...
77 rows

Postgres NOT IN Example:


SELECT product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM order_details 
						WHERE quantity > 99);

//Output
product_name
"Chai"
"Aniseed Syrup"
"Chef Anton's Cajun Seasoning"
...
...
57 rows

 ALL

Syntax:


expression operator ALL (subquery);

The right-hand side is a parenthesized subquery, which must return exactly one column.

The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result.

The result of ALL is “true” if all rows yield true (including the special case where the subquery returns no rows). The result is “false” if any false result is found.

NOT IN is equivalent to <> ALL.

If there are no failures but at least one right-hand row yields null for the operator’s result, the result of the ALL construct will be null, not true. 

Detailed Syntax:


SELECT column(s)
FROM table
WHERE column operator ALL
(SELECT column FROM table WHERE condition);

Postgres ALL Operator Example


SELECT product_name
FROM products
WHERE product_id = ALL (SELECT product_id FROM order_details 
						WHERE quantity = 10);

//Output 
product_name

0 rows

Sources

amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Jul 22 Is there a CSS parent selector? #Jul 22 Difference between :where and :is in CSS #Jul 22 Does :is() pseudo selector hint at CSS preprocessing in the future? #Jul 22 Control Scrolling with CSS Scroll Snap #Jul 21 Control rendering using CSS content-visibility property #Jul 21 How to use @supports rule in CSS?
You might also like these
How to replace HTML lists using CSS Counters?CSSKubernetes vs. Docker? It’s a misleading phraseMiscellaneousHow to uninstall Cocoapods from the Mac OS?MiscellaneousKilling A Project Part 3: How can an organization ensure that a doomed project is killed as early as possible?MiscellaneousHow to add Google Static Map using ACF map field?WordPressGap in Flex?CSS