實例簡析SQL嵌套子查詢：
一些初級程序員常常對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%')));
