Oracle 简单子查询
顾名思义，简单子查询是嵌套在 SQL 语句中的另一个SELECT 语句，并且子查询只返回一列数据
1，单行子查询：
子查询 (内查询) 在主查询之前一次执行完成。子查询的结果被主查询(外查询)使用 ，单行子查询，一个子查询语句只返回一行结果，不能返回空值
可以使用>,<,<>(!=),=,<=,>=
select select_list from table
where expr operation --operation为条件语句表达式，
(select select_list
from table
where expr);
例1：查询工资比Abel高的人
select first_name||' '||last_name name,salary
from employees
where salary >
(select salary from employees --该子查询只返回salary一个值
where lower(last_name) = 'abel');
NAME SALARY
---------------------- ----------
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Nancy Greenberg 12000
John Russell 14000
Karen Partners 13500
Alberto Errazuriz 12000
Lisa Ozer 11500
Michael Hartstein 13000
Shelley Higgins 12000
例2：查询最低工资大于50号部门最低工资的部门id和其最低工资
select nvl(department_id,1) department_id,min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary)
from employees
where department_id = 50);
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
1 7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
例3，单行子查询中使用单行函数
显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则 为’USA’。
select employee_id,last_name,department_id,
(case when department_id = (select department_id
from departments
where location_id = 1800
)
then 'USA'
else 'Canada' end) location
from employees
order by department_id;
或
select employee_id,last_name,department_id,
(case department_id when (select department_id
from departments
where location_id = 1800
)
then 'USA'
else 'Canada' end) location
from employees
order by department_id;
或
select employee_id,last_name,department_id,
decode(department_id,(select department_id
from departments
where location_id = 1800
),'USA',
'Canada') location
from employees
order by department_id;
2，多行子查询：
子查询返回多行值，可使用多行比较符：in(not in)(等于/不等于返回值的任意一个),any(和返回值的某一个值比较),all(和返回值的所有值比较)
例1:查询job_id不为为‘IT_PROG’，并且工资比任意一个job_id为‘IT_PROG’的人都低的员工信息;
或者工资比所有job_id为‘IT_PROG’的人都低的员工信息;
或者工资等于任意一个job_id为‘IT_PROG’的人都低的员工信息;
SQL> select employee_id,first_name||' '||last_name name,job_id,salary
from employees
where salary < any(
select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
EMPLOYEE_ID NAME JOB_ID SALARY
----------- --------------------- ---------- ----------
132 TJ Olson ST_CLERK 2100
136 Hazel Philtanker ST_CLERK 2200
128 Steven Markle ST_CLERK 2200
135 Ki Gee ST_CLERK 2400
127 James Landry ST_CLERK 2400
191 Randall Perkins SH_CLERK 2500
182 Martha Sullivan SH_CLERK 2500
144 Peter Vargas ST_CLERK 2500
...(省略部分)
SQL> select employee_id,first_name||' '||last_name name,job_id,salary
from employees
where salary < all(
select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
EMPLOYEE_ID NAME JOB_ID SALARY
----------- ----------------- -------------------- ----------
185 Alexis Bull SH_CLERK 4100
192 Sarah Bell SH_CLERK 4000
193 Britney Everett SH_CLERK 3900
188 Kelly Chung SH_CLERK 3800
137 Renske Ladwig ST_CLERK 3600
189 Jennifer Dilly SH_CLERK 3600
141 Trenna Rajs ST_CLERK 3500
186 Julia Dellinger SH_CLERK 3400
133 Jason Mallin ST_CLERK 3300
...(省略部分)
SQL> select employee_id,first_name||' '||last_name name,job_id,salary
from employees
where salary in (
select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
EMPLOYEE_ID NAME JOB_ID SALARY
----------- -------------------- -------------------- ----------
158 Allan McEwen SA_REP 9000
152 Peter Hall SA_REP 9000
109 Daniel Faviet FI_ACCOUNT 9000
202 Pat Fay MK_REP 6000
184 Nandita Sarchand SH_CLERK 4200
更多例题：
1. 查询工资最低的员工信息: last_name, salary
select last_name,salary
from employees
where salary = (
select min(salary)
from employees);
2. 查询平均工资最低的部门信息
SQL> select d.*,city
from departments d,locations ll
where ll.location_id = d.location_id
and d.department_id = (
select department_id
from employees
group by department_id
having avg(salary) = ( select min(avg(salary))
from employees
group by department_id));
3*. 查询平均工资最低的部门信息和该部门的平均工资
select d.*,
(select min(avg(salary)) from employees group by department_id) avg_salary,
city
from departments d,locations ll
where ll.location_id = d.location_id
and d.department_id = (
select department_id
from employees
group by department_id
having avg(salary) = ( select min(avg(salary))
from employees
group by department_id));
4. 查询平均工资最高的 job 信息
select * from jobs
where job_id in (
select job_id from employees
group by job_id
having avg(salary) = (
select max(avg(salary))
from employees
group by job_id));
5. 查询平均工资高于公司平均工资的部门有哪些?
select department_id from employees
group by department_id
having avg(salary) >
(select avg(salary) from employees);
6. 查询出公司中所有 manager 的详细信息.
select * from employees
where employee_id in (
select distinct(manager_id)
from employees);
7. 查询各个部门中的最高工资，找出其中中最低的最高工资是那个部门，并查询其最低工资是多少
select department_id,min(salary)
from employees
where department_id in (
select department_id
from employees
group by department_id
having max(salary) in (
select min(max(salary))
from employees
group by department_id))
group by department_id;
8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
select last_name,department_id,email,salary --根据manager_id查询相关信息
from employees
where employee_id in (
select distinct(manager_id) --根据平均工资最高的部门ID，查询这个部门的有哪些manager
from employees
where department_id = (
select department_id --查询平均工资等于最高的部门的ID
from employees
group by department_id
having avg(salary) = (
select max(avg(salary)) --查询平均工资最高的
from employees
group by department_id)));
9. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
select *
from employees
where salary in (
select max(salary)
from employees
where employee_id in (
select employee_id
from employees
where to_char(hire_date,'YYYY') = '1999'))
and to_char(hire_date,'YYYY') = '1999';
本博文系学习尚硅谷网易云课堂课程整理而成。