Oracle DB 使用子查詢來解決查詢

標籤：同義字   技術分享   alt   ==   常見問題   尋找   return   程式員   樣本   

? 定義子查詢? 描述子查詢可以解決的問題類型? 列出子查詢的類型? 編寫單行和多行子查詢 ? 子查詢：類型、文法和準則? 單行子查詢：– 子查詢中的組函數– 帶有子查詢的HAVING子句? 多行子查詢– 使用ALL或ANY運算子? 使用EXISTS運算子? 子查詢中的空值 

• 使用子查詢解決問題

誰的薪金高於Abel 的薪金？子查詢：Abel 的薪金是多少？主查詢：哪些僱員的薪金高於Abel 的薪金？ 使用子查詢解決問題假設要編寫一個查詢來找出誰的薪金高於Abel 的薪金。要解決此問題，需要使用兩個查詢：一個查詢用於尋找Abel 的薪金，另一個查詢用於尋找薪金超過該金額的人員。通過組合這兩個查詢，即將一個查詢放在另一個查詢中，可以解決此問題。內部查詢（即子查詢）會返回一個外部查詢（即主查詢）要使用的值。使用子查詢等同於執行兩個連續的查詢，而且將第一個查詢的結果用作第二個查詢中的搜尋值。 

• 子查詢文法

? 先執行子查詢（內部查詢），再執行主查詢（外部查詢）。? 主查詢會使用子查詢的結果。

SELECT select_list FROM table WHERE expr operator(SELECT select_list FROM table);

 

  子查詢是一個SELECT語句，它嵌入到另一個SELECT語句的子句中。通過使用子查詢，可以用簡單的語句構建功能強大的語句。當需要從表中選擇行，而選擇條件卻取決於該表自身中的資料時，子查詢非常有用。可以在許多SQL 子句中使用子查詢，其中包括以下子句：? WHERE子句? HAVING子句? FROM子句在該文法中：operator包括比較條件，例如>、=或IN註：比較條件分為以下兩類：單行運算子（>、=、>=、<、<>、<=）和多行運算子（IN、ANY、ALL、EXISTS）。子查詢通常被稱為嵌套SELECT語句、子SELECT語句或內部SELECT語句。通常先執行子查詢，然後使用其輸出來完善主查詢（即外部查詢）的查詢條件。 

• 使用子查詢

SELECT last_name, salary FROM employeesWHERE salary >(SELECT salary FROM employees WHERE last_name = ‘Abel‘);

 

 在樣本中，內部查詢確定了僱員Abel 的薪金。外部查詢會採用內部查詢的結果並根據此結果顯示薪金超過僱員Abel 的所有僱員。 

• 使用子查詢的準則

? 子查詢必須放在括弧中。? 子查詢放在比較條件的右側可增加可讀性。但是，子查詢可出現在比較子的任意一側。? 在子查詢中可以使用兩類比較條件：單行運算子和多行運算子。 對單行子查詢使用單行運算子，對多行子查詢使用多行運算子。 

• 子查詢的類型

? 單行子查詢：從內部SELECT語句中僅返回一行的查詢? 多行子查詢：從內部SELECT語句中返回多行的查詢註：此外，還有多列子查詢，此類查詢從內部SELECT語句中返回多個列。 

• 單行子查詢

僅返回一行使用單行比較子 單行子查詢是從內部SELECT語句中返回一行的一種查詢。此類子查詢使用單行運算子。給出了單行運算子的列表。樣本：顯示其職務ID 與僱員141 的職務ID 相同的僱員：

SELECT last_name, job_id FROM employeesWHERE job_id =(SELECT job_id FROM employees WHERE employee_id = 141);  

 

• 執行單行子查詢

SELECT last_name,job_id, salary FROM employeesWHERE job_id = (SELECT job_id FROM employees WHERE last_name= ‘Taylor‘)AND salary >(SELECT salary FROM employees WHERE last_name= ‘Taylor‘); 

 

可以將SELECT語句看作一個查詢塊。樣本顯示其職務與Taylor 相同但薪金高於Taylor 的僱員。該樣本由三個查詢塊組成：一個外部查詢和兩個內部查詢。先執行內部查詢塊，產生的查詢結果分別為SA_REP和8600。然後可以處理外部查詢塊，使用內部查詢返回的值來完善其搜尋條件。兩個內部查詢都返回單個值（分別為SA_REP和8600），因此將此SQL 陳述式稱為單行子查詢。註：外部查詢和內部查詢可以從不同的表中獲得資料。 

• 在子查詢中使用組函數

SELECT last_name,job_id, salary FROM employeesWHERE salary = (SELECT MIN(salary) FROM employees); 

 

通過在子查詢中使用組函數來返回單行，可以從主查詢中顯示資料。子查詢包含在括弧中，位於比較條件之後。樣本顯示其薪金等於最低薪金的所有僱員的姓氏、職務ID 和薪金。MIN組函數將單個值(2500) 返回給外部查詢。 

• 帶有子查詢的HAVING子句

? Oracle Server 會先執行子查詢。? Oracle Server 會將結果返回到主查詢的HAVING子句中。

SELECT department_id, MIN(salary) FROM employees GROUP BY department_idHAVING MIN(salary) >(SELECT MIN(salary) FROM employees WHERE department_id = 50); 

 

 不僅可以在WHERE子句中使用子查詢，還可以在HAVING子句中使用子查詢。Oracle 伺服器會執行子查詢，並將結果返回到主查詢的HAVING子句中。樣本中的SQL 陳述式顯示最低薪金高於部門50 的最低薪金的所有部門。樣本：尋找具有最低平均薪金的職務。

SELECT job_id, AVG(salary) FROM employees GROUP BY job_idHAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id);

 

 

• 此語句中有什麼錯誤

對多行子查詢使用了單行運算子

Connected.hr@TEST0924> SELECT employee_id, last_name FROM employees  2  WHERE salary =  3  (SELECT MIN(salary) FROM employees GROUP BY department_id);(SELECT MIN(salary) FROM employees GROUP BY department_id) *ERROR at line 3:ORA-01427: single-row subquery returns more than one row

 

 子查詢的常見錯誤是單行子查詢返回了多行。在樣本的SQL 陳述式中，子查詢包含一個GROUP BY子句，這意味著該子查詢將返回多行，每行都對應於一個它找到的組。這種情況下，子查詢的結果為4400、6000、2500、4200、7000、17000和8300。外部查詢將採用這些結果，並在其WHERE子句中使用它們。該WHERE子句包含一個等於(=) 運算子，該運算子是一個只需要一個值的單行比較子。=運算子無法接受子查詢中的多個值，因此產生了錯誤。要更正此錯誤，請將=運算子更改為IN。 

• 內部查詢沒有返回任何行

因為沒有名為“Haas”的僱員，所以子查詢沒有返回任何行。

hr@TEST0924> SELECT last_name, job_id FROM employees  2  WHERE job_id =  3  (SELECT job_id FROM employees WHERE last_name = ‘Haas‘); 

 

no rows selected 子查詢的另外常見問題是內部查詢沒有返回任何行。在樣本的SQL 陳述式中，子查詢包含一個WHERE子句。據推測，該語句的目的是要尋找名為Haas 的僱員。該語句正確，但是因為沒有名為“Haas”的僱員，所以執行時沒有選擇任何行。因此，子查詢不會返回任何行。外部查詢採用子查詢的結果（空值），並在其WHERE子句中使用這些結果。外部查詢沒有找到職務ID 等於空值的僱員，因此不會返回任何行。即使存在值為空白的職務，也不會返回行，因為兩個空值的比較會產生一個空值，從而使WHERE條件不為“真”。 

• 多行子查詢

? 返回多個行? 使用多行比較子  返回多行的子查詢被稱為多行子查詢。應對多行子查詢使用多行運算子，而不是使用單行運算子。多行運算子需要一個或多個值：

SELECT last_name, salary, department_id FROM employeesWHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

 

樣本：尋找其薪金等於各個部門最低薪金的僱員。先執行內部查詢，產生一個查詢結果。然後可以處理主查詢塊，使用內部查詢返回的值完善其搜尋條件。事實上，主查詢將以下面的形式出現在Oracle Server 上：

SELECT last_name, salary, department_id FROM employeesWHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

 

 

• 在多行子查詢中使用ANY運算子

SELECT employee_id, last_name, job_id, salary FROM employeesWHERE salary < ANY(SELECT salary FROM employees WHERE job_id = ‘IT_PROG‘)AND job_id <> ‘IT_PROG‘;

 

  ANY運算子（及其同義字SOME運算子）用於將某個值與子查詢返回的每個值進行比較。樣本顯示不是IT 程式員且薪金低於任一IT 程式員的僱員。程式員的最高薪金為$9,000。? <ANY表示低於最高值。? >ANY表示高於最低值。? =ANY等同於IN。參考：http://blog.csdn.net/rlhua/article/details/12007309  

• 在多行子查詢中使用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‘;  在多行子查詢中使用ALL運算子ALL運算子用於將某個值與子查詢返回的每個值進行比較。樣本顯示薪金低於職務ID 為IT_PROG的任何僱員的薪金且職務不是IT_PROG的僱員。>ALL表示大於最高值，而<ALL表示小於最低值。NOT運算子可以與IN、ANY和ALL運算子一起使用。參考：http://blog.csdn.net/rlhua/article/details/12006433  使用EXISTS運算子

SELECT * FROM departments WHERE NOT EXISTS(SELECT * FROM employees WHERE employees.department_id=departments.department_id);

 

 使用EXISTS運算子在查詢中使用EXISTS運算子，查詢結果取決於某些行是否在表中存在。如果子查詢至少返回一行，則求值結果為TRUE。樣本顯示沒有僱員的部門。對於DEPARTMENTS表中每一行，檢查條件，看一看在EMPLOYEES表中是否存在具有相同部門ID 的行。如果不存在這樣的行，則相應行滿足條件，從而選擇該行。如果EMPLOYEES表中存在相應行，則不選擇該行。 

• 子查詢中的空值

SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN(SELECT mgr.manager_id FROM employees mgr);

 

 樣本中的SQL 陳述式嘗試顯示沒有任何下屬的所有僱員。邏輯上，此SQL 陳述式應該返回12 行。但是，該SQL 陳述式沒有返回任何行。因為內部查詢返回的值中有一個為空白值，所以整個查詢不會返回任何行。原因是所有條件與空值進行比較後都會產生一個空值。因此，只要子查詢的結果集中可能包含空值，就不要使用NOT IN運算子。NOT IN運算子等同於<> ALL。請注意，如果使用的是IN運算子，則子查詢的結果集中存在空值就不會成為問題。IN運算子等同於=ANY。例如，要顯示具有下屬的僱員，可以使用下面的SQL 陳述式：

SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN(SELECT mgr.manager_id FROM employees mgr);

 

另外，可以在子查詢中包括WHERE子句，用來顯示沒有下屬的所有僱員：

SELECT last_name FROM employees WHERE employee_id NOT IN(SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

 

 

• 小結

子查詢是一個SELECT語句，它嵌入到另一個SQL 陳述式的子句中。當查詢基於帶有未知中間值的搜尋標準時，使用子查詢非常有協助。子查詢具有以下特性：? 可以將一行資料傳遞給包含單行運算子（如=、<>、>、>=、<或<=）的主語句? 可以將多行資料傳遞給包含多行運算子（如IN）的主語句? Oracle Server 先處理子查詢，隨後WHERE或HAVING子句會使用產生的結果? 可以包含組函數

Oracle DB 使用子查詢來解決查詢