EXISTS 和 INNER JOIN 案例

root@localhost:>SELECT /*+ shard_id(0) */
    ->        count(distinct a.orderno) as count
    ->   FROM order_main a
    ->   INNER JOIN order_delivery b
    ->     ON a.companyno = b.companyno
    ->  WHERE a.companyno=1430
    ->    AND a.packtype <> 2
    ->    AND a.delflg='N'
    ->    AND a.orderdt BETWEEN '2015-04-08 00:00:00' AND '2015-04-21 23:59:59'
    ->    AND b.shipdt  BETWEEN '2015-04-01 00:00:00' AND '2015-04-21 23:59:59'
    ->    LIMIT 5001
    -> ;
+-------+
| count |
+-------+
|  3692 |
+-------+
1 row in set (1 min 43.42 sec)



root@localhost:>SELECT /*+ shard_id(1) */
    ->        count(distinct a.orderno)
    -> FROM order_main a
    -> WHERE
    ->        a.companyno=1430
    ->    AND a.packtype <> 2
    ->    AND a.delflg='N'
    ->    AND a.orderdt BETWEEN '2015-04-08 00:00:00' AND '2015-04-21 23:59:59'
    -> AND EXISTS(
    ->            select 'A'
    ->              from order_delivery b
    ->             where b.companyno=1430
    ->               AND b.shipdt     BETWEEN '2015-04-01 00:00:00' AND '2015-04-21 23:59:59'
    ->            );
+---------------------------+
| count(distinct a.orderno) |
+---------------------------+
|                      3692 |
+---------------------------+
1 row in set (0.03 sec)