Subquery Expressions
Subquery expressions mainly include the following types:
- EXISTS/NOT EXISTS
Figure 1 shows the syntax of an EXISTS/NOT EXISTS expression.
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.
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.
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.
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)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot