#查询job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
job_id =(SELECT
job_id
FROM
employees
WHERE
employee_id =141)AND salary >(SELECT
salary
FROM
employees
WHERE
employee_id =143);
包含聚合函数的单行子查询
#查询最低工资大于110号部门最低工资的部门id和其最低工资SELECT
department_id,MIN(salary)FROM
employees
WHERE
department_id ISNOTNULLGROUPBY
department_id
HAVINGMIN(salary)>(SELECTMIN(salary)FROM
employees
WHERE
department_id =110);
包含流程控制函数的单行子查询
-- 查询员工的employee_id,last_name和location。-- 其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。SELECT
employee_id,
last_name,CASE department_id
WHEN(SELECT
department_id
FROM
departments
WHERE
location_id =1800)THEN'Canada'ELSE'USA'END location
FROM
employees;
空值问题
#已知manager_id有为null的数据SELECT
last_name
FROM
employees
WHERE
employee_id NOTIN(SELECT
manager_id
FROM
employees
);-- 返回结果为空SELECT last_name, job_id
FROM employees
WHERE job_id =(SELECT job_id
FROM employees
WHERE last_name ='Haas');-- 返回结果为空
使用子查询时,首先需确定子查询中是否含有NULL值
多行子查询
IN
#查询公司管理者的employee_id,last_name,job_id,department_id信息SELECT
employee_id,
last_name,
job_id,
department_id
FROM
employees
WHERE
employee_id IN(SELECTDISTINCT
manager_id
FROM
employees
WHERE
manager_id ISNOTNULL);
ANY
SELECT …FROM… WHERE expr > ANY(…): 即expr大于子查询中的任意一个
-- 查询其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salarySELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE
job_id !='IT_PROG'AND salary <ANY(SELECT
salary
FROM
employees
WHERE
job_id ='IT_PROG');
ALL
SELECT …FROM… WHERE expr > ALL(…): 即expr大于子查询中的每一个
# 查询工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salarySELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary >ALL(SELECT
salary
FROM
employees
WHERE
job_id ='SA_MAN');
SOME
同ANY
EXISTS
检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False
EXISTS执行顺序
首先执行一次外部查询,并缓存结果集,如 SELECT * FROM A
遍历外部查询结果集的每一行记录R,代入子查询中作为条件进行查询,如 SELECT 1 FROM B WHERE B.id = A.id
#题目:查询公司管理者的employee_id,last_name,job_id,department_id信息SELECT
e.employee_id,
e.last_name,
e.job_id,
e.department_id
FROM
employees e
WHEREEXISTS(SELECT
manager_id
FROM
employees
WHERE
manager_id = e.employee_id
);
# 查询departments表中,不存在于employees表中的部门的department_id和department_nameSELECT
d.department_id,
d.department_name
FROM
departments d
WHERENOTEXISTS(SELECT
department_id
FROM
employees
WHERE
department_id = d.department_id
);
COUNT(*)子查询
-- 查询每个部门下的部门人数大于 5 的部门名称SELECT
department_name
FROM
departments d
WHERE5<(SELECTCOUNT(1)FROM
employees
WHERE
department_id = d.department_id
);
不同查询语句查询同一结果
查询平均工资最低的部门信息
方式一
#查询平均工资最低的部门信息SELECT*FROM
departments
WHERE
department_id =(SELECT
department_id
FROM
employees
GROUPBY
department_id
HAVINGAVG(salary)=(SELECTMIN(avg_salary)AS min_salary
FROM(SELECTAVG(salary) avg_salary
FROM
employees
WHERE
department_id ISNOTNULLGROUPBY
department_id
) department_avg
));
方式二:ALL
#查询平均工资最低的部门信息SELECT*FROM
departments
WHERE
department_id =(SELECT
department_id
FROM
employees
GROUPBY
department_id
HAVINGAVG(salary)<=ALL(SELECTAVG(salary)FROM
employees
WHERE
department_id ISNOTNULLGROUPBY
department_id
));
方式三:LIMIT
#方式3: LIMITSELECT*FROM
departments
WHERE
department_id =(SELECT
department_id
FROM
employees
GROUPBY
department_id
HAVINGAVG(salary)=(SELECTAVG(salary)FROM
employees
WHERE
department_id ISNOTNULLGROUPBY
department_id
ORDERBYAVG(salary)ASCLIMIT0,1));
方式四:子查询作为FROM条件
SELECT*FROM
departments d,(SELECT
department_id,AVG(salary)FROM
employees
WHERE
department_id ISNOTNULLGROUPBY
department_id
ORDERBYAVG(salary)ASCLIMIT1) s
WHERE
d.department_id = s.department_id;