#子查询-某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候就要用到子查询。用于子查询的关键字主要包括：

in、not in、=、!=、exists、not exists等等。

#从emp表中查询出所有部门在dept表中的所有记录

mysql> select * from dept;

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

| deptno | deptname |

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

|      1 | tech     |

|      2 | sale     |

|      3 | hr       |

|      4 | sl       |

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

4 rows in set (0.01 sec)

mysql> select * from emp;

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

| ename | birth      | hirdate    | sal     | deptno | age1 |

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

| zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |

| zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |

| ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |

| ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |

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

4 rows in set (0.00 sec)

mysql> select * from emp where deptno in(select deptno from dept);

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

| ename | birth      | hirdate    | sal     | deptno | age1 |

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

| zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |

| ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |

| zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |

| ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |

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

4 rows in set (0.00 sec)

#如果子查询记录数唯一，还可以用=代替in

mysql> select * from emp where deptno=(select deptno from dept limit 1);

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

| ename | birth | hirdate | sal | deptno | age1 |

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

| zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 |

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

1 row in set (0.00 sec)

# 某些情况下，子查询可转化为表连接

mysql> select * from emp where deptno in(select deptno from dept);

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

| ename | birth | hirdate | sal | deptno | age1 |

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

| zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 |

| ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 |

| zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 |

| ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 |

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

4 rows in set (0.00 sec)

#转换为表连接后

mysql> select emp.* from emp,dept where emp.deptno=dept.deptno;

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

| ename | birth      | hirdate    | sal     | deptno | age1 |

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

| zzx1  | 2000-01-01 | 2000-01-01 | 2000.00 |      1 |   21 |

| ssss  | 2019-01-01 | 2018-01-01 | 5000.00 |      2 |   24 |

| zzx1  | 2002-03-09 | 2009-04-03 | 2001.00 |      3 |   22 |

| ttx2  | 2023-04-10 | 2010-03-04 | 4000.00 |      4 |   23 |

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

4 rows in set (0.00 sec)

#union和union all的主要区别是union all是把结果集直接合并在一起

union是将union all后的结果在进行一次distinct，去除重复记录后的结果。

mysql> select deptno from emp union all select deptno from dept;

+--------+

| deptno |

+--------+

|      1 |

|      3 |

|      4 |

|      2 |

|      1 |

|      2 |

|      3 |

|      4 |

+--------+

8 rows in set (0.00 sec)

mysql> select deptno from emp union  select deptno from dept;

+--------+

| deptno |

+--------+

|      1 |

|      3 |

|      4 |

|      2 |

+--------+

4 rows in set (0.00 sec)