16.子查询
1.定义子查询
--子查询语法
----在主查询之前，子查询会执行一次
----子查询结果被用于主查询
hr@ORCLPDB01 2023-02-19 17:21:42> select last_name,salary
2 from employees
3 where salary > ( select salary
4 from employees
5 where last_name = 'Abel');
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Greenberg 12008
Russell 14000
Partners 13500
Errazuriz 12000
Ozer 11500
Hartstein 13000
Higgins 12008
10 rows selected.
Elapsed: 00:00:00.012.正确使用子查询
--子查询要用括号括起来
--习惯把子查询放在比较操作符右边
--单行操作用单行子查询，多行用多行子查询
--子查询中不需要order by 除非执行TOP-N查询
3.单行与多行子查询
--any 小于最大值，大于最小值
hr@ORCLPDB01 2023-02-19 17:36:40> select employee_id,last_name,job_id,salary
2 from employees
3 where salary < any ( select salary
4 from employees
5 where job_id = 'IT_PROG')
6 and job_id <> 'IT_PROG';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
132 Olson ST_CLERK 2100
136 Philtanker ST_CLERK 2200
128 Markle ST_CLERK 2200
135 Gee ST_CLERK 2400
127 Landry ST_CLERK 2400
191 Perkins SH_CLERK 2500
182 Sullivan SH_CLERK 2500
144 Vargas ST_CLERK 2500
140 Patel ST_CLERK 2500
131 Marlow ST_CLERK 2500
119 Colmenares PU_CLERK 2500
118 Himuro PU_CLERK 2600
198 OConnell SH_CLERK 2600
199 Grant SH_CLERK 2600
143 Matos ST_CLERK 2600
126 Mikkilineni ST_CLERK 2700
139 Seo ST_CLERK 2700
117 Tobias PU_CLERK 2800
183 Geoni SH_CLERK 2800
130 Atkinson ST_CLERK 2800
195 Jones SH_CLERK 2800
190 Gates SH_CLERK 2900
116 Baida PU_CLERK 2900
134 Rogers ST_CLERK 2900
197 Feeney SH_CLERK 3000
187 Cabrio SH_CLERK 3000
115 Khoo PU_CLERK 3100
196 Walsh SH_CLERK 3100
142 Davies ST_CLERK 3100
181 Fleaur SH_CLERK 3100
194 McCain SH_CLERK 3200
138 Stiles ST_CLERK 3200
125 Nayer ST_CLERK 3200
180 Taylor SH_CLERK 3200
129 Bissot ST_CLERK 3300
133 Mallin ST_CLERK 3300
186 Dellinger SH_CLERK 3400
141 Rajs ST_CLERK 3500
137 Ladwig ST_CLERK 3600
189 Dilly SH_CLERK 3600
188 Chung SH_CLERK 3800
193 Everett SH_CLERK 3900
192 Bell SH_CLERK 4000
185 Bull SH_CLERK 4100
184 Sarchand SH_CLERK 4200
200 Whalen AD_ASST 4400
124 Mourgos ST_MAN 5800
202 Fay MK_REP 6000
173 Kumar SA_REP 6100
179 Johnson SA_REP 6200
167 Banda SA_REP 6200
166 Ande SA_REP 6400
123 Vollman ST_MAN 6500
203 Mavris HR_REP 6500
165 Lee SA_REP 6800
113 Popp FI_ACCOUNT 6900
161 Sewall SA_REP 7000
178 Grant SA_REP 7000
155 Tuvault SA_REP 7000
164 Marvins SA_REP 7200
172 Bates SA_REP 7300
171 Smith SA_REP 7400
154 Cambrault SA_REP 7500
160 Doran SA_REP 7500
111 Sciarra FI_ACCOUNT 7700
112 Urman FI_ACCOUNT 7800
122 Kaufling ST_MAN 7900
153 Olsen SA_REP 8000
159 Smith SA_REP 8000
120 Weiss ST_MAN 8000
121 Fripp ST_MAN 8200
110 Chen FI_ACCOUNT 8200
206 Gietz AC_ACCOUNT 8300
177 Livingston SA_REP 8400
176 Taylor SA_REP 8600
175 Hutton SA_REP 8800
76 rows selected.
Elapsed: 00:00:00.01
--all 小于最小值，大于最大值
hr@ORCLPDB01 2023-02-19 17:37:40> l
1 select employee_id,last_name,job_id,salary
2 from employees
3 where salary < any ( select salary
4 from employees
5 where job_id = 'IT_PROG')
6* and job_id <> 'IT_PROG'
hr@ORCLPDB01 2023-02-19 17:39:52> 3
3* where salary < any ( select salary
hr@ORCLPDB01 2023-02-19 17:40:00> c/any/all
3* where salary < all ( select salary
hr@ORCLPDB01 2023-02-19 17:40:06> l
1 select employee_id,last_name,job_id,salary
2 from employees
3 where salary < all ( select salary
4 from employees
5 where job_id = 'IT_PROG')
6* and job_id <> 'IT_PROG'
hr@ORCLPDB01 2023-02-19 17:40:07> r
1 select employee_id,last_name,job_id,salary
2 from employees
3 where salary < all ( select salary
4 from employees
5 where job_id = 'IT_PROG')
6* and job_id <> 'IT_PROG'
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
185 Bull SH_CLERK 4100
192 Bell SH_CLERK 4000
193 Everett SH_CLERK 3900
188 Chung SH_CLERK 3800
137 Ladwig ST_CLERK 3600
189 Dilly SH_CLERK 3600
141 Rajs ST_CLERK 3500
186 Dellinger SH_CLERK 3400
133 Mallin ST_CLERK 3300
129 Bissot ST_CLERK 3300
180 Taylor SH_CLERK 3200
138 Stiles ST_CLERK 3200
125 Nayer ST_CLERK 3200
194 McCain SH_CLERK 3200
115 Khoo PU_CLERK 3100
196 Walsh SH_CLERK 3100
142 Davies ST_CLERK 3100
181 Fleaur SH_CLERK 3100
187 Cabrio SH_CLERK 3000
197 Feeney SH_CLERK 3000
116 Baida PU_CLERK 2900
190 Gates SH_CLERK 2900
134 Rogers ST_CLERK 2900
183 Geoni SH_CLERK 2800
130 Atkinson ST_CLERK 2800
117 Tobias PU_CLERK 2800
195 Jones SH_CLERK 2800
126 Mikkilineni ST_CLERK 2700
139 Seo ST_CLERK 2700
143 Matos ST_CLERK 2600
199 Grant SH_CLERK 2600
118 Himuro PU_CLERK 2600
198 OConnell SH_CLERK 2600
140 Patel ST_CLERK 2500
131 Marlow ST_CLERK 2500
119 Colmenares PU_CLERK 2500
191 Perkins SH_CLERK 2500
182 Sullivan SH_CLERK 2500
144 Vargas ST_CLERK 2500
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
132 Olson ST_CLERK 2100
44 rows selected.
Elapsed: 00:00:00.004.exists操作
hr@ORCLPDB01 2023-02-19 17:41:40> select * from departments
2 where not exists
3 (select * from employees where employees.department_id = departments.department_id);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
16 rows selected.
Elapsed: 00:00:00.00
