求订购货品为"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 |
+----+--------------------+------------+------+-----------------+---------+---------+---------------------+------+-------------------------------------------+
阅读(4908) | 评论(0) | 转发(0) |
