Expressions（2）
Tags: beginners, opengauss, sql
Published: 2023-03-06 04:37:56

**Subquery expressions include the following types:**

· EXISTS/NOT EXISTS

Figure 8 shows the syntax of an **EXISTS/NOT EXISTS** expression.

Figure 8 EXISTS/NOT EXISTS::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/otgd97y78p9udjiv7mg0.png)

The parameter of an **EXISTS** expression is an arbitrary **SELECT** statement, or a subquery. The subquery is computed to determine whether it returns any rows. If it returns at least one row, the result of **EXISTS** is **true**. If it returns no rows, the result of **EXISTS** is **false**.

The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.

Example:

Note: In the following query statement, **tpcds** indicates the schema name.

openGauss=# SELECT sr_reason_sk,sr_customer_sk FROM tpcds.store_returns WHERE EXISTS (SELECT d_dom FROM tpcds.date_dim WHERE d_dom = store_returns.sr_reason_sk and sr_customer_sk <10);
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wlhn74kdxnoexkpbyq3a.png)

· IN/NOT IN

Figure 9 shows the syntax of an **IN/NOT IN** expression.

Figure 9 IN/NOT IN::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mjq8abzzo1zppl6kae2r.png)

The right-side parenthesized subquery returns only one column. The left-side expression is calculated and compared to each row of the subquery result. The result of **IN** is **true** if any equal subquery row is found. The result is **false** if no equal row is found (including the case where the subquery returns no rows).

This is in accordance with SQL normal rules for Boolean combinations of null values. If the columns corresponding to two rows equal and are not null, the two rows are equal to each other. If any columns corresponding to the two rows do not equal and are not null, the two rows are not equal to each other. Otherwise, the result is **NULL**. If the result of each row does not equal and at least one row yields **NULL**, the result of **IN** will be **NULL**.

Example:

Note: In the following query statement, tpcds indicates the schema name
openGauss=# SELECT sr_reason_sk,sr_customer_sk  FROM tpcds.store_returns WHERE sr_customer_sk IN (SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10);
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r5b9rfj7d75rknwxw13n.png)
· ANY/SOME

Figure 10 shows the syntax of an **ANY/SOME** expression.

Figure 10 any/some::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hjubazecqmneo8i95ptl.png)

The right-side parenthesized subquery returns only one column. The left-side expression is calculated and compared to each row of the subquery result by using a 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 case where the subquery returns no rows). **SOME** is a synonym of **ANY**. **IN** can be equivalently replaced by **ANY**.

Example:

Note: In the following query statement, **tpcds** indicates the schema name.
openGauss=# SELECT sr_reason_sk,sr_customer_sk  FROM tpcds.store_returns WHERE sr_customer_sk < ANY (SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10);
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t481i6jiwy3h3vues2lt.png)

· ALL

Figure 11 shows the syntax of an **ALL** expression.

Figure 11 all::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kdv2c0hoob0ija4edws.png)

openGauss=# SELECT sr_reason_sk,sr_customer_sk  FROM tpcds.store_returns WHERE sr_customer_sk < all(SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10);
 sr_reason_sk | sr_customer_sk 
--------------+----------------
(0 rows)