SQL - 子查詢解決查詢問題

上一頁
下一頁

子查詢最好定義為查詢中的查詢。子查詢使您能夠編寫查詢，這些查詢根據在查詢執行時實際開發的條件選擇資料行。更正式地說，它是在另一個 SELECT 語句的一個子句中使用 SELECT 語句。實際上，子查詢可以包含在另一個子查詢中，而另一個子查詢又包含在另一個子查詢中，依此類推。子查詢還可以巢狀在 INSERT、UPDATE 和 DELETE 語句中。子查詢必須用括號括起來。

只要子查詢返回單個值，它就可以在允許使用表示式的任何地方使用。這意味著返回單個值的子查詢也可以在 FROM 子句列表中列為物件。這被稱為內聯檢視，因為當子查詢用作 FROM 子句的一部分時，它被視為虛擬表或檢視。子查詢可以放在主查詢的 FROM 子句、WHERE 子句或 HAVING 子句中。

Oracle 允許在 WHERE 子句中最多巢狀 255 個子查詢級別。對於在 FROM 子句中表達的巢狀子查詢，沒有限制。實際上，255 個級別的限制並不是真正的限制，因為很少遇到巢狀超過三到四個級別的子查詢。

子查詢 SELECT 語句與用於開始常規查詢或外部查詢的 SELECT 語句非常相似。子查詢的完整語法是

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

子查詢型別

單行子查詢：返回單行輸出的子查詢。在 WHERE 條件中使用時，它們表示單行比較運算子的使用。

多行子查詢：返回多行輸出的子查詢。它們使用多行比較運算子，如 IN、ANY、ALL。也可以有多列返回的子查詢。

相關子查詢：相關子查詢依賴於外部查詢提供的資料。此型別的子查詢還包括使用 EXISTS 運算子測試滿足指定條件的資料行是否存在子查詢。

單行子查詢

當外部查詢的結果基於單個未知值時，使用單行子查詢。儘管這種查詢型別正式被稱為“單行”，但名稱意味著查詢返回多列，但只有一行結果。但是，單行子查詢只能向外部查詢返回僅包含一列的一行結果。

在下面的 SELECT 查詢中，內部 SQL 只返回一行，即公司的最低工資。它反過來使用此值來比較所有員工的工資，並僅顯示工資等於最低工資的員工。

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

當需要根據某些條件限制查詢的分組結果時，使用 HAVING 子句。如果必須將子查詢的結果與分組函式進行比較，則必須將內部查詢巢狀在外層查詢的 HAVING 子句中。

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary)
			FROM employees)

多行子查詢

多行子查詢是巢狀查詢，可以向父查詢返回多行結果。多行子查詢最常用於 WHERE 和 HAVING 子句中。因為它返回多行，所以必須由集合比較運算子（IN、ALL、ANY）處理。雖然 IN 運算子的含義與前面章節中討論的相同，但 ANY 運算子將指定值與子查詢返回的每個值進行比較，而 ALL 運算子將值與子查詢返回的每個值進行比較。

以下查詢顯示單行子查詢返回多行時出現的錯誤。

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id
			FROM employees
			WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

多行運算子的使用

  • [> ALL] 大於子查詢返回的最高值

  • [< ALL] 小於子查詢返回的最低值

  • [< ANY] 小於子查詢返回的最高值

  • [> ANY] 大於子查詢返回的最低值

  • [= ANY] 等於子查詢返回的任何值（與 IN 相同）

可以使用 IN 運算子將上述 SQL 重寫如下。

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

請注意，在上面的查詢中，IN 匹配子查詢返回的部門 ID，將其與主查詢中的部門 ID 進行比較，並返回滿足條件的員工姓名。

連線將是上述查詢的更好解決方案，但為了說明目的，已在其中使用了子查詢。

相關子查詢

與常規子查詢（外部查詢依賴於內部查詢提供的值）相反，相關子查詢是指內部查詢依賴於外部查詢提供的值的子查詢。這意味著在相關子查詢中，內部查詢會重複執行，對於外部查詢可能選擇的每一行執行一次。

相關子查詢可以生成能夠回答複雜管理問題的結果表。

考慮下面的 SELECT 查詢。與前面考慮的子查詢不同，此 SELECT 語句中的子查詢不能獨立於主查詢解析。請注意，外部查詢指定從具有別名 e1 的員工表中選擇行。內部查詢將員工表的員工部門編號列 (DepartmentNumber) 與別名 e2 與別名表名 e1 的同一列進行比較。

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

多列子查詢

多列子查詢向外部查詢返回多列，並且可以在外部查詢的 FROM、WHERE 或 HAVING 子句中列出。例如，以下查詢顯示當前工資在 1000 到 2000 範圍內且在 10 或 20 部門工作的員工的歷史詳細資訊。

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

當多列子查詢用於外部查詢的 FROM 子句中時，它會建立一個臨時表，其他子句可以引用該臨時表。此臨時表更正式地稱為內聯檢視。子查詢的結果被視為 FROM 子句中的任何其他表。如果臨時表包含分組資料，則分組子集將被視為表中的單獨資料行。考慮以下查詢中的 FROM 子句。子查詢形成的內聯檢視是主查詢的資料來源。

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);
列印頁面
上一頁
下一頁
廣告