1、子查詢語法

主查詢執行前，子查詢先執行，而且只執行一次
主查詢使用子查詢的結果

例子：查詢薪水比Abel高的人員的名字和薪水

SQL> select last_name,salary

2 from employees

3 where salary>(select salary from employees where last_name='Abel');

LAST_NAME SALARY

------------------------- ----------

Hartstein 13000

使用子查詢的注意事項：

子查詢需要使用括號括起來
子查詢一般放在比較條件的右邊
只有在進行Top-N分析的時候，子查詢才可以使用order by進行排序，其他情況都不行
對單行子查詢使用單行運算子，對多行子查詢使用多行運算子

例子：子查詢如果不加括號會報錯

SQL> select last_name,salary

2 from employees

3 where salary>select salary from employees where last_name='Abel';

where salary>select salary from employees where last_name='Abel'

*

ERROR at line 3:

ORA-00936: missing expression

例子：子查詢可以放在比較條件的左邊，不過一般都放在右邊，便於閱讀和理解。

SQL> select last_name,salary

2 from employees

3 where (select salary from employees where last_name='Abel')<salary; </salary;<>

LAST_NAME SALARY

------------------------- ----------

Hartstein 13000

例子：一般情況下子查詢加上order by進行排序會報錯

SQL> select last_name,salary

2 from employees

3 where salary>(select salary from employees where last_name='Abel' order by 1);

where salary>(select salary from employees where last_name='Abel' order by 1)

*

ERROR at line 3:

ORA-00907: missing right parenthesis

例子：查詢薪水最大的10名員工，在進行Top-N分析的時候，子查詢才可以使用order by進行排序

SQL> select *

2 from (select last_name,salary from employees order by 2 desc)

3 where rownum<11;

LAST_NAME SALARY

------------------------- ----------

King 24000

2、子查詢型別

單行子查詢，只返回一行結果
多行子查詢，返回多行結果

（1）單行子查詢

單行子查詢比較運算子

例子：查詢job_id與employee_id為141號員工相同，薪水比143號員工大的人員

SQL> select last_name,job_id,salary

2 from employees

3 where job_id=(select job_id from employees where employee_id=141)

4 and salary>(select salary from employees where employee_id=143);

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

Nayer ST_CLERK 3200

在子查詢裡面使用組函式

例子：查詢最低薪水員工的資訊

SQL> select last_name,job_id,salary

2 from employees

3 where salary=(select min(salary) from employees);

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

Olson ST_CLERK 2100

在having子句使用子查詢

例子：查詢部門最小薪水比50部門最小薪水大的部門

SQL> select department_id,min(salary)

2 from employees

3 group by department_id

4 having min(salary)>(select min(salary) from employees where department_id=50);

DEPARTMENT_ID MIN(SALARY)

------------- -----------

100 6900

如果將上面語句子查詢中的where替換成group by就會報錯，因為使用group by後，子查詢結果為多行，而“>”為單行運算子

SQL> select department_id,min(salary)

2 from employees

3 group by department_id

4 having min(salary)>(select min(salary) from employees group by department_id=50);

having min(salary)>(select min(salary) from employees group by department_id=50)

*

ERROR at line 4:

ORA-00907: missing right parenthesis

（2）多行子查詢

如果子查詢結果返回多行，則需要使用多行運算子，再使用單行運算子會報錯

例子：子查詢返回多行，使用單行運算子報錯

SQL> select employee_id,last_name from employees

2 where salary=(select min(salary) from employees group by department_id);

where salary=(select min(salary) from employees group by department_id)

*

ERROR at line 2:

ORA-01427: single-row subquery returns more than one row

如果子查詢返回0行，則主查詢也返回0行，但是在單行子查詢中，透過使用“=”進行判斷的時候，即使主查詢返回0行，也不能說明子查詢返回0行，因為子查詢有可能返回的是null，而null是不能使用“=”進行判斷的。

例子：查詢與人員編號100有相同的管理人員的員工資訊

SQL> select last_name,manager_id from employees

2 where manager_id=(select manager_id from employees where employee_id=100);

no rows selected

子查詢的結果為null

SQL> select manager_id from employees where employee_id=100;

MANAGER_ID

----------

多行子查詢比較運算子

前面都可以使用not

在多行子查詢中使用any運算子

例子：查詢比職位為“IT_PROG”的任何一位員工的薪水低的員工資訊（實際上就是比職位為“IT_PROG”的最大薪水的員工薪水小，小於最大的，如果是大於any，就是大於最小的）

SQL> select employee_id,last_name,job_id,salary from employees

2 where salary<any(select salary="" from="" employees="" where="" job_id="IT_PROG" )

3 and job_id<>'IT_PROG';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

132 Olson ST_CLERK 2100

在多行子查詢中使用all運算子

例子：查詢比職位為“IT_PROG”的所有員工的薪水低的員工資訊（實際上就是比職位為“IT_PROG”的最小薪水的員工薪水小，小於最小的，如果是大於all，就是大於最大的）

SQL> select employee_id,last_name,job_id,salary from employees

2 where salary<all(select salary="" from="" employees="" where="" job_id="IT_PROG" )

3 and job_id<>'IT_PROG';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

185 Bull SH_CLERK 4100

在多行子查詢中使用in或者not in運算子

例子：查詢是管理人員的員工姓名和不是管理人員的員工名字

SQL> select emp.last_name from employees emp

2 where emp.employee_id in (select mgr.manager_id from employees mgr);

LAST_NAME

-------------------------

Zlotkey

18 rows selected.

SQL> select emp.last_name from employees emp

2 where emp.employee_id not in (select mgr.manager_id from employees mgr);

no rows selected

這裡因為如果你使用in只需要和子查詢返回結果的任何一個匹配都可以，而使用not in則需要和子查詢返回結果的所有進行比較，包括null，而與null進行計算，都為null，故結果為0行，可以先在子查詢中排除null

SQL> select emp.last_name from employees emp

2 where emp.employee_id not in (select mgr.manager_id from employees mgr where mgr.manager_id is not null);

LAST_NAME

-------------------------

Whalen

89 rows selected.

3、相關習題

（1）View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104 When you execute the statement it does not produce the desired output. What is the reason for this ?

A.The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B.The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
C.The WHERE clause condition in the main query is using the=comparison operator, instead of the =ANY operator.
D.The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.

答案：D

（2）View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id? What would happen when the above statement is executed?

A.The statement would execute successfully to produce the required output.

B.The statement would not execute because inline views and outer joins cannot be used together.

C.The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D.The statement would not execute because the GROUP BY clause cannot be used in the inline view.

答案：Ａ

（3）A non-?correlated subquery can be defined as ____.

A.a set of sequential queries, all of which must always return a single value

B.a set of sequential queries, all of which must return values from the same table

C.a SELECT statement that can be embedded in a clause of another SELECT statement only

D.a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

答案：D

（4）Which two statements are true regarding operators used with subqueries? (Choose two.)

A. The NOT IN operator is equivalent to IS NULL.

B. The <any operator="" means="" less="" than="" the="" maximum.

C. =ANY and =ALL operators have the same functionality.

D. The IN operator cannot be used in single-row subqueries.

E. The NOT operator can be used with IN, ANY and ALL operators.

答案：BE

（5）Which two statements are true regarding multiple?row subqueries? (Choose two.)

A.They can contain group functions.

B.They always contain a subquery within a subquery.

C.They use the < ALL operator to imply less than the maximum.

D.They can be used to retrieve multiple rows from a single table only.

E.They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

答案：AE

（6）View the Exhibit and examine the structure of the EMPLOYEES table. You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of 'Neena'. Which SQL statement would give you the desired result?

A.SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

B.SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

C.SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

D.SELECT first_name, salary FROM employees WHERE ( manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena' ) AND salary >= ( SELECT salary FROM employees WHERE first_name = 'Neena' ) ) AND first_name <> 'Neena';

答案：D

（7）View the Exhibit and examine the structure of the ORDERS table. You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101. Which query would give you the desired output?

A.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
B.SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROMorders WHERE customer_id = 101);
C.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D.SELECT order_id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer_id = 101);

答案：C

（8）View the Exhibit and examine the data in the PRODUCT_INFORMATION table. Which two tasks would require subqueries？(Choose two.)

A.displaying the minimum list price for each product status.
B.displaying all supplier IDs whose average list price is more than 500.
C.displaying the number of products whose list prices are more than the average list price.
D.displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable.
E.displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE.

答案：CD

（9）Which two statements are true regarding subqueries？(Choose two.)
A.Only two subqueries can be placed at one level.
B.A subquery can be used to access data from one or more tables or views.
C.If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.
D.The columns in a subquery must always be qualified with the name or alias of the table used.
E.A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.

答案：BC

（10）View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

A. displaying the total order value for sales representatives 161 and 163

B. displaying the order total for sales representative 161 in the year 1999

C. displaying the number of orders that have order mode online and order date in 1999

D. displaying the number of orders whose order total is more than the average order total for all online orders

答案：D

（11）Which two statements are true regarding subqueries？(Choose two.)

A.The ORDER BY clause can be used in the subquery.
B.A subquery can be used in the FROM clause of a SELECT statement.
C.If the subquery returns NULL, the main query may still return result rows.
D.A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause.
E.Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

答案：AB

OCP課程8：SQL之使用子查詢

相關文章