MySQL数据库，子查询学习，高手必备(四)

⾏⼦查询（⼦查询结果集⼀⾏多列）

示例

查询员⼯编号最⼩并且⼯资最⾼的员⼯信息，3种⽅式。/*查询员⼯编号最⼩并且⼯资最⾼的员⼯信息*/

/*①查询最⼩的员⼯编号*/

SELECT min(employee_id)

FROM employees;

/*②查询最⾼⼯资*/

SELECT max(salary)

FROM employees;

/*③⽅式1：查询员⼯信息*/

SELECT *

FROM employees a

WHERE a.employee_id = (SELECT min(employee_id)

FROM employees)

AND salary = (SELECT max(salary)

FROM employees);

/*⽅式2*/

SELECT *

FROM employees a

WHERE (a.employee_id, a.salary) = (SELECT

min(employee_id),

max(salary)

FROM employees);

/*⽅式3*/

SELECT *

FROM employees a

WHERE (a.employee_id, a.salary) in (SELECT

min(employee_id),

max(salary)

FROM employees);

⽅式1⽐较常见，⽅式2、3更简洁。

exists后⾯（也叫做相关⼦查询）

1. 语法：exists(完整的查询语句)。

2. exists查询结果：1或0，exists查询的结果⽤来判断⼦查询的结果集中是否有值。3. ⼀般来说，能⽤exists的⼦查询，绝对都能⽤in代替，所以exists⽤的少。

4. 和前⾯的查询不同，这先执⾏主查询，然后主查询查询的结果，在根据⼦查询进⾏过

滤，⼦查询中涉及到主查询中⽤到的字段，所以叫相关⼦查询。

示例1

简单⽰例

mysql> SELECT exists(SELECT employee_id

FROM employees

WHERE salary = 300000) AS 'exists返回1或者0';

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

| exists返回1或者0 |

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

| 0 |

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

1 row in set (0.00 sec)

示例2

查询所有员⼯的部门名称

/*exists⼊门案例*/

SELECT exists(SELECT employee_id

FROM employees

WHERE salary = 300000) AS 'exists返回1或者0';

/*查询所有员⼯部门名*/

SELECT department_name

FROM departments a

WHERE exists(SELECT 1

FROM employees b

WHERE a.department_id = b.department_id);

/*使⽤in实现*/

SELECT department_name

FROM departments a

WHERE a.department_id IN (SELECT department_id

FROM employees);示例3

查询没有员⼯的部门

/*查询没有员⼯的部门*/

/*exists实现*/

SELECT *

FROM departments a

WHERE NOT exists(SELECT 1

FROM employees b

WHERE a.department_id = b.department_id AND

b.department_id IS NOT NULL);

/*in的⽅式*/

SELECT *

FROM departments a

WHERE a.department_id NOT IN (SELECT department_id

FROM employees b

WHERE b.department_id IS NOT NULL);

上⾯脚本中有b.department_id IS NOT NULL，为什么，有⼤坑，向下看。

NULL的⼤坑

示例1

使⽤not in的⽅式查询没有员⼯的部门，如下：

SELECT *

FROM departments a

WHERE a.department_id NOT IN (SELECT department_id

FROM employees b);

运⾏结果：

mysql> SELECT *

-> FROM departments a

-> WHERE a.department_id NOT IN (SELECT department_id

-> FROM employees b);

Empty set (0.00 sec)not in的情况下，⼦查询中列的值为NULL的时候，外查询的结果为空。

建议：建表是，列不允许为空。