exists-versus-in-operators.md
EXISTS versus IN
Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks. For example, the following query gets a list of all the orders placed by all stores on January 2, 2007 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 vd JOIN store.store_orders_fact ord ON vd.vendor_key = ord.vendor_key)
AND date_ordered = '2007-01-02';
store_key | order_number | date_ordered
-----------+--------------+--------------
114 | 271071 | 2007-01-02
19 | 290888 | 2007-01-02
132 | 58942 | 2007-01-02
232 | 9286 | 2007-01-02
126 | 224474 | 2007-01-02
196 | 63482 | 2007-01-02
...
196 | 83327 | 2007-01-02
138 | 278373 | 2007-01-02
179 | 293586 | 2007-01-02
155 | 213413 | 2007-01-02
(506 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 2, 2007:
=> SELECT store_key, order_number, date_ordered, vendor_name
FROM store.store_orders_fact ord JOIN public.vendor_dimension vd ON ord.vendor_key = vd.vendor_key
WHERE vd.deal_size IN (SELECT MAX(deal_size) FROM public.vendor_dimension) AND date_ordered = '2007-01-02';
store_key | order_number | date_ordered | vendor_name
-----------+--------------+--------------+----------------------
50 | 99234 | 2007-01-02 | Everything Wholesale
81 | 200802 | 2007-01-02 | Everything Wholesale
115 | 13793 | 2007-01-02 | Everything Wholesale
204 | 41842 | 2007-01-02 | Everything Wholesale
133 | 169025 | 2007-01-02 | Everything Wholesale
163 | 208580 | 2007-01-02 | Everything Wholesale
29 | 154972 | 2007-01-02 | Everything Wholesale
145 | 236790 | 2007-01-02 | Everything Wholesale
249 | 54838 | 2007-01-02 | Everything Wholesale
7 | 161536 | 2007-01-02 | Everything Wholesale
(10 rows)