MySQL基础篇-6-子查询

子查询的结构

SELECT select_list
FROM tab
WHERE expr operator (SELECT select_list FROM tab);

子查询分类

按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 和 多行子查询

单行子查询

单行比较符

1. 查询工资大于149号员工工资的员工信息

-- 查询工资大于149号员工工资的员工信息
SELECT last_name
FROM employees
WHERE salary > ( -- 工资大于149号员工的工资
		SELECT salary 
		FROM employees
		WHERE employe_id = 149);

2. 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT LAST_NAME, JOB_ID, SALARY
FROM employees
WHERE job_id = (
		-- job_id == 141号员工	
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
			   )
AND 
	salary > (
		-- salary > 143号员工
		SELECT salary 
		FROM employees
		WHERE employee_id = 143
    		 );

3. 返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary 
FROM employees
WHERE salary = (
		-- MIN(salary)
		SELECT MIN(salary)
		FROM employees
			   );

4. 查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (
			SELECT manager_id
			FROM employees
			WHERE employee_id IN (141, 174)
					)
AND  
    department_id IN (
			SELECT department_id
			FROM employees
			WHERE employee_id IN (141, 174)
    				 )
AND 
	employee_id NOT IN (141, 174);

5. 查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
			-- 50号部门最低工资
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 50
					);

多行子查询

1. 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id,salary
FROM employees
WHERE salary < ANY (
		-- job_id 为'IT_PROG'部门的工资
		SELECT salary 
		FROM employees
		WHERE job_id = 'IT_PROG'
				   )
AND
	job_id <> 'IT_PROG';				   

2. 返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id,salary
FROM employees
WHERE salary < ALL (
		-- job_id 为'IT_PROG'部门的工资
		SELECT salary 
		FROM employees
		WHERE job_id = 'IT_PROG'
				   )
AND
	job_id <> 'IT_PROG';

3. 查询平均工资最低的部门id

SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) = (
		-- 最低平均工资
		SELECT MIN(avg_sal)
		FROM (
			-- 按部门分组，求各部门平均工资
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			) dept_avg_sal
					)

相关子查询

关联查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次

SELECT column1, column2, ...
FROM tab1 outer
WHERE column1 operator (
		SELECT column1, column2
		FROM tab2
		WHERE expr1 = outer.expr2;
	)

1. 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

-- 相关子查询
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (
		-- 本部平均工资
		SELECT AVG(salary)
		FROM employees
		WHERE department_id = outer.department_id
		);
               
-- FROM 中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;          

2. 查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name
    FROM departments d
    WHERE e.`department_id` = d.`department_id`
);

3. 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同 id的员工的employee_id,last_name和其job_id

SELECT employee_id, last_name, e.job_id
FROM employee e
WHERE 2 <= (
	SELECT COUNT(*)
	FROM job_history
	WHERE employee_id = e.employee_id
	);

EXISTS 与 NOT EXISTS

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行

1. 查询公司管理者的employee_id，last_name，job_id，department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (SELECT *
              FROM employees e2
              WHERE e2.manager_id = e1.employee_id);

2. 查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM employees
                  WHERE department_id = d.department_id);