oracle+非关联子查询,关联子查询与非关联子查询

求订购货品为"orange"的客户名。

1) 联表查询，不用子查询

mysql> explain select distinct c.cust_name

from customers c join orders o on c.cust_id=o.cust_id

join order_details od on o.order_id=od.order_id

join articles a on od.art_id=a.art_id

where a.art_name='orange';

+----+-------------+-------+--------+-----------------+---------+---------+------------------------+------+------------------------------+

| id | select_type | table | type   | possible_keys   | key     | key_len | ref                    | rows | Extra                        |

+----+-------------+-------+--------+-----------------+---------+---------+------------------------+------+------------------------------+

|  1 | SIMPLE      | a     | ALL    | PRIMARY         | NULL    | NULL    | NULL                   |    3 | Using where; Using temporary |

|  1 | SIMPLE      | od    | ref    | PRIMARY,art_id  | art_id  | 4       | ue_raymond.a.art_id    |    1 | Using index                  |

|  1 | SIMPLE      | o     | eq_ref | PRIMARY,cust_id | PRIMARY | 4       | ue_raymond.od.order_id |    1 |                              |

|  1 | SIMPLE      | c     | eq_ref | PRIMARY         | PRIMARY | 4       | ue_raymond.o.cust_id   |    1 |                              |

+----+-------------+-------+--------+-----------------+---------+---------+------------------------+------+------------------------------+

4 rows in set (0.00 sec)

2)使用关联子查询

mysql> explain select cust_name from customers

where cust_id in

(select o.cust_id from orders o

where exists

(select null from order_details od, articles a

where a.art_name='orange'

and od.art_id=a.art_id and o.order_id=od.order_id));

+----+--------------------+-----------+----------------+----------------+---------+---------+-----------------------+------+--------------------------+

| id | select_type        | table     | type           | possible_keys  | key     | key_len | ref                   | rows | Extra                    |

+----+--------------------+-----------+----------------+----------------+---------+---------+-----------------------+------+--------------------------+

|  1 | PRIMARY            | customers | ALL            | NULL           | NULL    | NULL    | NULL                  |    4 | Using where              |

|  2 | DEPENDENT SUBQUERY | o         | index_subquery | cust_id        | cust_id | 4       | func                  |    1 | Using index; Using where |

|  3 | DEPENDENT SUBQUERY | od        | ref            | PRIMARY,art_id | PRIMARY | 4       | ue_raymond.o.order_id |    1 | Using index              |

|  3 | DEPENDENT SUBQUERY | a         | eq_ref         | PRIMARY        | PRIMARY | 4       | ue_raymond.od.art_id  |    1 | Using where              |

+----+--------------------+-----------+----------------+----------------+---------+---------+-----------------------+------+--------------------------+

4 rows in set (0.00 sec)

3)使用非关联子查询A

select cust_name from customers c where cust_id in

(select distinct o.cust_id from orders o

where o.order_id in

(select od.order_id from order_details od join articles a on od.art_id=a.art_id and a.art_name='orange'));

mysql> explain select cust_name from customers c where cust_id in

-> (select distinct o.cust_id from orders o

-> where o.order_id in

-> (select od.order_id from order_details od join articles a on od.art_id=a.art_id and a.art_name='orange'));

+----+--------------------+-------+----------------+----------------+---------+---------+----------------------+------+--------------------------+

| id | select_type        | table | type           | possible_keys  | key     | key_len | ref                  | rows | Extra                    |

+----+--------------------+-------+----------------+----------------+---------+---------+----------------------+------+--------------------------+

|  1 | PRIMARY            | c     | ALL            | NULL           | NULL    | NULL    | NULL                 |    4 | Using where              |

|  2 | DEPENDENT SUBQUERY | o     | index_subquery | cust_id        | cust_id | 4       | func                 |    1 | Using index; Using where |

|  3 | DEPENDENT SUBQUERY | od    | ref            | PRIMARY,art_id | PRIMARY | 4       | func                 |    1 | Using where; Using index |

|  3 | DEPENDENT SUBQUERY | a     | eq_ref         | PRIMARY        | PRIMARY | 4       | ue_raymond.od.art_id |    1 | Using where              |

+----+--------------------+-------+----------------+----------------+---------+---------+----------------------+------+--------------------------+

4)使用非关联子查询B

select cust_name from customers c where cust_id in

(select distinct o.cust_id from orders o,

(select od.order_id from order_details od join articles a on od.art_id=a.art_id and a.art_name='orange') sub_od

where o.order_id=sub_od.order_id);

mysql> explain select cust_name from customers c where cust_id in

-> (select distinct o.cust_id from orders o,

-> (select od.order_id from order_details od join articles a on od.art_id=a.art_id and a.art_name='orange') sub_od

-> where o.order_id=sub_od.order_id);

+----+--------------------+------------+------+-----------------+---------+---------+---------------------+------+-------------------------------------------+

| id | select_type        | table      | type | possible_keys   | key     | key_len | ref                 | rows | Extra                                     |

+----+--------------------+------------+------+-----------------+---------+---------+---------------------+------+-------------------------------------------+

|  1 | PRIMARY            | c          | ALL  | NULL            | NULL    | NULL    | NULL                |    4 | Using where                               |

|  2 | DEPENDENT SUBQUERY | o          | ref  | PRIMARY,cust_id | cust_id | 4       | func                |    1 | Using where; Using index; Using temporary |

|  2 | DEPENDENT SUBQUERY | | ALL  | NULL            | NULL    | NULL    | NULL                |    2 | Using where; Distinct                     |

|  3 | DERIVED            | a          | ALL  | PRIMARY         | NULL    | NULL    | NULL                |    3 | Using where                               |

|  3 | DERIVED            | od         | ref  | art_id          | art_id  | 4       | ue_raymond.a.art_id |    1 | Using index                               |

+----+--------------------+------------+------+-----------------+---------+---------+---------------------+------+-------------------------------------------+