EXISTS and NOT EXISTS

转自:https://my.vertica.com/docs/5.1.6/HTML/index.htm#10379.htm

EXISTS and NOT EXISTS

The EXISTS predicate is one of the most common predicates used to build conditions that use noncorrelated and correlated subqueries. Use EXISTS to identify the existence of a relationship without regard for the quantity. For example, EXISTS returns true if the subquery returns any rows, and NOT EXISTS returns true if the subquery returns no rows.

[NOT] EXISTS subqueries take the following form:

expression [ NOT ] EXISTS ( subquery )

The EXISTS condition is considered to be met if the subquery returns at least one row. Since the result depends only on whether any records are returned, and not on the contents of those records, the output list of the subquery is normally uninteresting. A common coding convention is to write all EXISTS tests as follows:

EXISTS (SELECT 1 WHERE ...) 

In the above fragment, SELECT 1 returns the value 1 for every record in the query. If the query returns, for example, five records, it returns 5 ones. The system doesn't care about the real values in those records; it just wants to know if a row is returned.

Alternatively, a subquery’s select list that uses EXISTS might consist of the asterisk (*). You do not need to specify column names, because the query tests for the existence or nonexistence of records that meet the conditions specified in the subquery.

EXISTS (SELECT * WHERE ...) 

Notes

  • If EXISTS (subquery) returns at least 1 row, the result is TRUE.
  • If EXISTS (subquery) returns no rows, the result is FALSE.
  • If NOT EXISTS (subquery) returns at least 1 row, the result is FALSE.
  • If NOT EXISTS (subquery) returns no rows, the result is TRUE.

Examples

The following query retrieves the list of all the customers who purchased anything from any of the stores amounting to more than 550 dollars:

=> SELECT customer_key, customer_name, customer_state
   FROM public.customer_dimension WHERE EXISTS 
     (SELECT 1 FROM store.store_sales_fact 
      WHERE customer_key = public.customer_dimension.customer_key 
      AND sales_dollar_amount > 550)
   AND customer_state = 'MA' ORDER BY customer_key;
 
 customer_key |   customer_name    | customer_state 
--------------+--------------------+----------------
        14818 | William X. Nielson | MA
        18705 | James J. Goldberg  | MA
        30231 | Sarah N. McCabe    | MA
        48353 | Mark L. Brown      | MA
(4 rows)

Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks. For example, to get a list of all the orders placed by all stores on January 2, 2003 for vendors with records in the vendor table:

=> SELECT store_key, order_number, date_ordered
   FROM store.store_orders_fact WHERE EXISTS
     (SELECT 1 FROM public.vendor_dimension
      WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
   AND date_ordered = '2003-01-02';
 
 store_key | order_number | date_ordered 
-----------+--------------+--------------
        37 |         2559 | 2003-01-02
        16 |          552 | 2003-01-02
        35 |         1156 | 2003-01-02
        13 |         3885 | 2003-01-02
        25 |          554 | 2003-01-02
        21 |         2687 | 2003-01-02
        49 |         3251 | 2003-01-02
        19 |         2922 | 2003-01-02
        26 |         1329 | 2003-01-02
        40 |         1183 | 2003-01-02
(10 rows)

The above query looks for existence of the vendor and date ordered. To return a particular value, rather than simple existence, the query looks for orders placed by the vendor who got the best deal on January 4, 2004:

=> SELECT store_key, order_number, date_ordered
   FROM store.store_orders_fact ord, public.vendor_dimension vd
   WHERE ord.vendor_key = vd.vendor_key AND vd.deal_size IN
      (SELECT MAX(deal_size) FROM public.vendor_dimension)
    AND date_ordered = '2004-01-04';
 
 store_key | order_number | date_ordered
-----------+--------------+--------------
       166 |        36008 | 2004-01-04
       113 |        66017 | 2004-01-04
       198 |        75716 | 2004-01-04
        27 |       150241 | 2004-01-04
       148 |       182207 | 2004-01-04
         9 |       188567 | 2004-01-04
        45 |       202416 | 2004-01-04
        24 |       250295 | 2004-01-04
       121 |       251417 | 2004-01-04
(9 rows)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值