詳解SQL嵌套子查詢

2024-08-31 00:46:50

字體：大中小

來源：轉載

供稿：網友

一些初級程序員常常對SQL語法中的子查詢，由其對嵌套子查詢（子查詢中包含一個子查詢）的使用比較生疏，本文就此做一個基本講解，相信新手會有一定收獲。
    使用子查詢的原則
1.一個子查詢必須放在圓括號中。
2.將子查詢放在比較條件的右邊以增加可讀性。
子查詢不包含 ORDER BY 子句。對一個 SELECT 語句只能用一個 ORDER BY 子句,
并且如果指定了它就必須放在主 SELECT 語句的最后。
ORDER BY 子句可以使用,并且在進行 Top-N 分析時是必須的。
3.在子查詢中可以使用兩種比較條件：單行運算符和多行運算符。

    子查詢的類型
單行子查詢：從內 SELECT 語句只返回一行的查詢
多行子查詢：從內 SELECT 語句返回多行的查詢

    單行子查詢
單行子查詢是從內查詢返回一行的查詢。在該子查詢類型中用一個單行操作符。幻燈片中列出了單行操作符。
例

顯示那些 job ID 與雇員 141 相同的雇員。
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);

顯示 job ID 與雇員 141 相同,并且薪水高于雇員 143 的那些雇員。

注：外和內查詢可以從不同的表中取得數據。

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

求所有人誰的工資最小。

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

求每個部門的最小工資,但是要高于50號部門的工資。

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

問題出現在：單行子查詢返回了多個查詢值;
應改為：
SELECT employee_id, last_name
FROM employees
WHERE salary in
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = ’Haas’);

如果子查詢返回的是零值,不會對主程序造成影響;
如果子查詢返回的是空值,那么會影響主程序的返回值;
SELECT employee_id,
last_name
FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees)

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

放在select下的子查詢的返回值必須是一個具體值,
from后面也可以加子查詢;
having后面也可以加子查詢;
order by后面也可以;

多列子查詢適應于：成對比較;非成對比較。

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);

輸出：176 149 80

只有要查詢的東西和你子查詢返回的東西一一對應上了,你的查詢才能成功。
如果有一個對應不上那么你的查詢不會成功。

非成對的子查詢：
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN (SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

輸出：144 124 50
143 124 50
142 124 50
176 149 80
上面兩個程序就是成對子查詢和非成對子查詢兩者之間的區別。

如果我想去顯示員工信息,要求：員工的工資高于本部門的平均工資。
SELECT a.last_name,
a.salary,
a.department_id
FROM employees a
WHERE a.salary >
(SELECT AVG(salary)
FROM employees b
WHERE b.department_id = a.department_id);

in line view(內聯視圖)

SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;

數據分散于多個表中需要進行拼合
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber LIKE 'FW123%')));