Updated on 2025-08-25 GMT+08:00

Subquery Expressions

Subquery expressions mainly include the following types:

  • EXISTS/NOT EXISTS

    Figure 1 shows the syntax of an EXISTS/NOT EXISTS expression.

    Figure 1 EXISTS/NOT EXISTS::=

    The parameter of EXISTS is an arbitrary SELECT statement, or subquery. The system processes the subquery to determine whether it returns rows. If it returns at least one row, the result of EXISTS is true. If the subquery does not return any rows, the result of EXISTS is false.

    This subquery typically runs just enough to determine whether it can produce at least one row, rather than running to completion.

    Example:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    postgres=#SELECT sr_reason_sk,sr_customer_sk FROM schemastore_returns WHERE EXISTS (SELECT d_dom FROM schemadate_dim WHERE d_dom = store_returns.sr_reason_sk and sr_customer_sk <10);
    sr_reason_sk | sr_customer_sk 
    --------------+----------------
               13 |              2
               22 |              5
               17 |              7
               25 |              7
                3 |              7
               31 |              5
                7 |              7
               14 |              6
               20 |              4
                5 |              6
               10 |              3
                1 |              5
               15 |              2
                4 |              1
               26 |              3
    (15 rows)
    
  • IN/NOT IN

    Figure 2 shows the syntax of an IN/NOT IN expression.

    Figure 2 IN/NOT IN::=

    On the right is a parenthesized subquery that must return only one column. The left-hand expression computes and compares against each row of the subquery result. If any matching subquery row is found, the result of IN is true. If no matching row is found, the result is false (including the case where the subquery returns no rows).

    NULLs in expressions or subquery rows follow the rules SQL uses for handling combinations of Boolean values and NULLs. If the corresponding columns of two rows are both equal and non-null, then the two rows are equal. If any corresponding column is unequal and non-null, then the two rows are unequal. Otherwise, the result is unknown (NULL). If every row's result is either unequal or NULL, and there is at least one NULL, then the result of IN is NULL.

    Example:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    postgres=# SELECT sr_reason_sk,sr_customer_sk  FROM schemastore_returns WHERE sr_customer_sk IN (SELECT d_dom FROM schemadate_dim WHERE d_dom < 10);
    sr_reason_sk | sr_customer_sk 
    --------------+----------------
               10 |              3
               26 |              3
               22 |              5
               31 |              5
                1 |              5
               32 |              5
               32 |              5
                4 |              1
               15 |              2
               13 |              2
               33 |              4
               20 |              4
               33 |              8
                5 |              6
               14 |              6
               17 |              7
                3 |              7
               25 |              7
                7 |              7
    (19 rows)
    
  • ANY/SOME

    Figure 3 shows the syntax of an ANY/SOME expression.

    Figure 3 any/some::=

    On the right is a parenthesized subquery that must return only one column. The left-hand expression uses an operator to compute and compare against each row of the subquery result, which must be a Boolean value. If at least one true value is obtained, the result of ANY is true. If all results are false, the result is false (including the case where the subquery returns no rows). SOME is synonymous with ANY. IN and ANY can be used interchangeably.

    Example:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    postgres=#SELECT sr_reason_sk,sr_customer_sk  FROM schemastore_returns WHERE sr_customer_sk < ANY (SELECT d_dom FROM schemadate_dim WHERE d_dom < 10);
    sr_reason_sk | sr_customer_sk 
    --------------+----------------
               26 |              3
               17 |              7
               32 |              5
               32 |              5
               13 |              2
               31 |              5
               25 |              7
                5 |              6
                7 |              7
               10 |              3
                1 |              5
               14 |              6
                4 |              1
                3 |              7
               22 |              5
               33 |              4
               20 |              4
               33 |              8
               15 |              2
    (19 rows)
    
  • ALL

    Figure 4 shows the syntax of an ALL expression.

    Figure 4 all::=

    On the right is a parenthesized subquery that must return only one column. The left-hand expression uses an operator to compute and compare against each row of the subquery result, which must be a Boolean value. If all results are true, the result of ALL is true (including the case where the subquery returns no rows). If at least one false value is obtained, the result is false.

    Example:

    1
    2
    3
    4
    postgres=#SELECT sr_reason_sk,sr_customer_sk  FROM schemastore_returns WHERE sr_customer_sk < all(SELECT d_dom FROM schemadate_dim WHERE d_dom < 10);
     sr_reason_sk | sr_customer_sk 
    --------------+----------------
    (0 rows)