oracle子查询
10级学员 乔宇课堂总结
子查询语法,
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询使用 (外查询)。
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
查询员工名字,但是工资要大于名字为ABEL这个人的工资
结果为
LAST_NAME
-------------------------
Hartstein
Higgins
King
Kochhar
De Haan
Greenberg
Russell
Partners
Errazuriz
Ozer
注意事项: 子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询。
子查询分为两种:
单行子查询 : 返回的为一行
多行子查询 : 返回的为多行
其中单行子查询就是第一个例子了
我们看一下多行子查询
这是其中的操作符的一些作用
我们看 any这一个
SELECT employee_id, last_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 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
119 Colmenares PU_CLERK 2500
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
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
143 Matos ST_CLERK 2600
118 Himuro PU_CLERK 2600
198 OConnell SH_CLERK 2600
199 Grant SH_CLERK 2600
126 Mikkilineni ST_CLERK 2700
139 Seo ST_CLERK 2700
183 Geoni SH_CLERK 2800
195 Jones SH_CLERK 2800
130 Atkinson ST_CLERK 2800
117 Tobias PU_CLERK 2800
134 Rogers ST_CLERK 2900
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
116 Baida PU_CLERK 2900
190 Gates SH_CLERK 2900
187 Cabrio SH_CLERK 3000
197 Feeney 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
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
180 Taylor SH_CLERK 3200
133 Mallin ST_CLERK 3300
129 Bissot ST_CLERK 3300
186 Dellinger SH_CLERK 3400
141 Rajs ST_CLERK 3500
189 Dilly SH_CLERK 3600
137 Ladwig ST_CLERK 3600
188 Chung SH_CLERK 3800
193 Everett SH_CLERK 3900
192 Bell SH_CLERK 4000
185 Bull SH_CLERK 4100
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
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
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
113 Popp FI_ACCOUNT 6900
178 Grant SA_REP 7000
161 Sewall SA_REP 7000
155 Tuvault SA_REP 7000
164 Marvins SA_REP 7200
172 Bates SA_REP 7300
171 Smith SA_REP 7400
160 Doran SA_REP 7500
154 Cambrault SA_REP 7500
111 Sciarra FI_ACCOUNT 7700
112 Urman FI_ACCOUNT 7800
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
122 Kaufling ST_MAN 7900
159 Smith SA_REP 8000
120 Weiss ST_MAN 8000
153 Olsen SA_REP 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行
我们看一下第三种
All
SELECT employee_id, last_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 LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
198 OConnell SH_CLERK 2600
199 Grant SH_CLERK 2600
115 Khoo PU_CLERK 3100
116 Baida PU_CLERK 2900
117 Tobias PU_CLERK 2800
118 Himuro PU_CLERK 2600
119 Colmenares PU_CLERK 2500
125 Nayer ST_CLERK 3200
126 Mikkilineni ST_CLERK 2700
127 Landry ST_CLERK 2400
128 Markle ST_CLERK 2200
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
129 Bissot ST_CLERK 3300
130 Atkinson ST_CLERK 2800
131 Marlow ST_CLERK 2500
132 Olson ST_CLERK 2100
133 Mallin ST_CLERK 3300
134 Rogers ST_CLERK 2900
135 Gee ST_CLERK 2400
136 Philtanker ST_CLERK 2200
137 Ladwig ST_CLERK 3600
138 Stiles ST_CLERK 3200
139 Seo ST_CLERK 2700
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
140 Patel ST_CLERK 2500
141 Rajs ST_CLERK 3500
142 Davies ST_CLERK 3100
143 Matos ST_CLERK 2600
144 Vargas ST_CLERK 2500
180 Taylor SH_CLERK 3200
181 Fleaur SH_CLERK 3100
182 Sullivan SH_CLERK 2500
183 Geoni SH_CLERK 2800
185 Bull SH_CLERK 4100
186 Dellinger SH_CLERK 3400
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
187 Cabrio SH_CLERK 3000
188 Chung SH_CLERK 3800
189 Dilly SH_CLERK 3600
190 Gates SH_CLERK 2900
191 Perkins SH_CLERK 2500
192 Bell SH_CLERK 4000
193 Everett SH_CLERK 3900
194 McCain SH_CLERK 3200
195 Jones SH_CLERK 2800
196 Walsh SH_CLERK 3100
197 Feeney SH_CLERK 3000
已选择44行。
其中我们明白了如果是any的话,我们只要符合里面的任何一个条件就行,
但是all呢,他必须符合一定的特殊条件如要是>的话我们必须大于其条件的最大的,反之依然.
最后说一下子查询的空值的问题
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);
返回值为
未选定行
今天,就学习这个吧!好好体会一下
如有疑问,发送邮件到qiaoshemei@163.com
1446
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
