子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或者外查询
分类:
按子查询出现的位置:
SELECT 后面
仅仅支持标量子查询
FROM 后面
支持表子查询
WHERE或having后面
标量子查询
列子查询
行子查询
exists后面(相关子查询)
表子查询
按结果集的列数不同:
标量子查询(结果集只有一行多列)
列子查询(结果集只有一列多行)
行子查询(多行多列)
表子查询(一般为多行多列)
一、where或having后面
1、标量子查询(单行子查询)
2、列子查询(多行一列)
3、列子查询(多行多列)
特点:
子查询放在小括号内侧
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用
<>=<=>=
列子查询,一般搭配着多行操作符使用
in,any,some,all
子查询优先于主查询
1、标量子查询
案例:谁的工资比Abel高?
查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
查询员工的信息,满足salary>abel结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
案例:返回job_id与141号员工相同,salary比143号员工多的员工姓名,jio_id和工资
查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;
);
查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
查询员工的姓名,job_id和工资,要求job_id=141,salary>143
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
);
案例:返回公司工资最少的员工的last_name,job_id,和salary
查询公司的 最低工资
SELECT MIN(salary)
FROM employees;
查询last_name,job_id,和salary,要求工资比上面低
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
案例:查询最低工资大于100号部门最低工资的部门id和其最低工资
查询100号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=100;
查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
在2基础上筛选,满足min(salary)>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=100
);
2、列子查询
案例:返回location_id是1400或者1700的部门中的所有员工姓名
查询location_id是1400或者1700的部门
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700);
查询员工姓名,要求部门是1400,1700列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
第二种方法
SELECT last_name
FROM employees
WHERE department_id =ANY(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
案例:返回其他部门中比job_id为it_prog部门任意工资低的员
工的员工号、姓名、job_id以及salary
#查询job_id为iy_prog部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
查询员工号、姓名、job_id以及salary,salary<ANY(上述结果)
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
第二种方法
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE 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 DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
3、行子查询
案例:查询员工编号最小且工资最高的员工信息
查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
查询最高工资
SELECT MAX(salary)
FROM employees;
查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
) AND salary=(
SELECT MAX(salary)
FROM employees
);
第二种方法(行子查询)
SELECT *
FROM employees
WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)
FROM employees);