关联子查询与非关联子查询

时间：2008-10-17 来源：r_a

求订购货品为"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 | <derived3> | 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                               |
+----+--------------------+------------+------+-----------------+---------+---------+---------------------+------+-------------------------------------------+