⾏⼦查询(⼦查询结果集⼀⾏多列)
示例
查询员⼯编号最⼩并且⼯资最⾼的员⼯信息,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的时候,外查询的结果为空。
建议:建表是,列不允许为空。