深入浅出MySQL(4)-子查询

在查询时，有时查询的条件是另一个select的结果，这时就需要使用子查询，子查询的关键字有in、not in、=、！=、exists、not exists等。

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
|      5 | fin      |
+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from emp where deptno in (select deptno from dept);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

如果记录数唯一的话，in可以用”=”来代替，不唯一就会ERROR:

mysql> select * from emp where deptno = (select deptno from dept limit 1);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
+--------+------------+---------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp where deptno = (select deptno from dept);]
ERROR 1242 (21000): Subquery returns more than 1 row

某些情况子查询可以转换为表连接:

mysql> select * from emp,dept where emp.deptno = dept.deptno;
+--------+------------+---------+--------+--------+----------+
| ename  | hiredate   | sal     | deptno | deptno | deptname |
+--------+------------+---------+--------+--------+----------+
| zzx    | 2000-01-01 | 2000.00 |      1 |      1 | tech     |
| lisa   | 2003-02-01 | 4000.00 |      2 |      2 | sale     |
| bjguan | 2004-04-02 | 5000.00 |      1 |      1 | tech     |
| bzshen | 2005-04-01 | 4000.00 |      3 |      3 | hr       |
+--------+------------+---------+--------+--------+----------+
4 rows in set (0.00 sec)

uninon和union all 可以实现将将两个表查询的数据合并显示出来:

mysql> select deptno from emp
    -> union all
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      1 |
|      3 |
|      4 |
|      1 |
|      2 |
|      3 |
|      5 |
+--------+
9 rows in set (0.07 sec)

要想去掉重复之后显示的话:

mysql> select deptno from emp
    -> union
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
5 rows in set (0.00 sec)