作者:LAMP小白 点击:1926 发布日期:2012-10-28 14:35:00 返回列表
有相当一部分使用了子查询的查询命令可以被改写为一个联结查询,有时候,联结查询要比子查询的执行效率更高,所以把子查询改写为联结查询是个不坏的主意
1.如何改写用来选取匹配值的子查询
mysql> SELECT * FROM member WHERE student_id IN (SELECT student_id FROM absence WHERE date='2012-10-01');
+-----------+-----+------------+
| user_name | sex | student_id |
+-----------+-----+------------+
| mio | F | 1 |
| yum | F | 2 |
| hehe | M | 3 |
| kaka | M | 4 |
+-----------+-----+------------+
4 rows in set (0.00 sec)
我们可以把他简单的改成这样
mysql> SELECT * FROM member INNER JOIN absence ON member.student_id = absence.student_id AND absence.date = '2012-10-01';
+-----------+-----+------------+------------+------------+
| user_name | sex | student_id | student_id | date |
+-----------+-----+------------+------------+------------+
| mio | F | 1 | 1 | 2012-10-01 |
| yum | F | 2 | 2 | 2012-10-01 |
| hehe | M | 3 | 3 | 2012-10-01 |
| kaka | M | 4 | 4 | 2012-10-01 |
+-----------+-----+------------+------------+------------+
4 rows in set (0.00 sec)
只选取member的字段
mysql> SELECT member.* FROM member INNER JOIN absence ON member.student_id = absence.student_id AND absence.date = '2012-10-01';
+-----------+-----+------------+
| user_name | sex | student_id |
+-----------+-----+------------+
| mio | F | 1 |
| yum | F | 2 |
| hehe | M | 3 |
| kaka | M | 4 |
+-----------+-----+------------+
4 rows in set (0.00 sec)
在某些场合,子查询和关联查询可能返回不同的结果,比如table2包含多个column2a的实例,子查询职位每个column2a的值生成一个实例,联结操作会把他们都生成出来并导致在其输出里实现重复的数据行,如果想防止这种重复,使用SELECT DISTINCT即可
2.改写用来选取非匹配值的子查询
子查询的另一种用法师检索在一个表里有,而另一个表没有的值
mysql> SELECT * FROM member WHERE student_id NOT IN (SELECT student_id FROM absence WHERE date='2012-10-02')
-> ;
+-----------+-----+------------+
| user_name | sex | student_id |
+-----------+-----+------------+
| yum | F | 2 |
| hehe | M | 3 |
| kaka | M | 4 |
| ye | M | 5 |
+-----------+-----+------------+
4 rows in set (0.00 sec)
mysql> SELECT member.* FROM member LEFT JOIN absence ON member.student_id = absence.student_id AND absence.date = '2012-10-02' WHERE absence.student_id IS NULL;
+-----------+-----+------------+
| user_name | sex | student_id |
+-----------+-----+------------+
| yum | F | 2 |
| hehe | M | 3 |
| kaka | M | 4 |
| ye | M | 5 |
+-----------+-----+------------+
4 rows in set (0.00 sec)
上一篇:子查询 下一篇:快递查询API