5 子查询

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句，称为主查询或外查询

分类：

​ 按子查询的位置：

​ select后面：仅仅支持标量子查询

​ from后面：支持表子查询

​ ★where或having后面：标量子查询√ 列子查询√ 行子查询

​ exists 后面（相关子查询）

​ 按结果集的行列数不同：

​ 标量子查询（结果集只有一行一列）【单行子查询】

​ 列子查询（结果集只有一列多行）

​ 行子查询（结果集有一行多列）

​ 表子查询（结果集一般为多行多列）

where或having后的子查询

特点：

• 子查询放在小括号内

• 子查询一般放在条件的右侧

• 标量子查询，一般搭配着单行操作符

  ​ > < <= >= = <>

• 列子查询，一般搭配着多行操作符

  ​ in any/some all

• 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询（单行子查询）

标量子查询，一般搭配着单行操作符

​ > < <= >= = <>

# 案例： 谁的工资比abel 高
SELECT * 
FROM employees
WHERE salary > (

	SELECT salary
	FROM employees
	WHERE last_name	= 'Abel'
);

# 案例：返回job_id 与141员工相同，salary比143号员工多的员工 姓名，job_id 和工资
SELECT salary,job_id,last_name
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

#案例：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

#案例：查询最低工资大于50号部门最低工资的部门id和最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

2.列子查询（多行子查询）

列子查询，一般搭配着多行操作符

​ 返回多行

​ in/not in 等于列表中的任意一个

​ any/some 和子查询返回的某一个值比较 max

​ all 和子查询返回的所有值比较 min

# 案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

#案例：返回其他工种中比job_id为‘IT_PROG'工种任意工资低的员工的：工号、姓名、job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT salary   #SELECT MAX(salary) 
	FROM employees
	WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';


#案例：返回其他工种中比job_id为‘IT_PROG'工种所有工资都低的员工的工号、姓名、job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT salary  #SELECT MIN(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

3.行子查询（一行多列或多行多列）

用的较少

SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

select后面

仅仅支持标量子查询

# 案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id =d.department_id
)
FROM departments d;


# 案例：查询员工号=102的部门名  很麻烦一般用其他方式
SELECT(
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON e.department_id =d.department_id
	WHERE e.employee_id=102
)

from后面

表子查询：多行多列

#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.grade_level
FROM(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grade g
ON ag_dep.ag BETWEEN g.lowest_sal AND highest_sal

exists 后面（相关子查询）

exists

语法：

exists（完整的查询语句）

结果：

有值返回1

没有返回0

 #案例：查询有员工的部门名
  SELECT department_name
  FROM departments d
  WHERE EXISTS( 
	SELECT *
	FROM employees e
	WHERE d.department_id=e.id
  );

#案例：查询没有女朋友的男生信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);