MySQL範例講解子查詢的使用

2023-03-02 18:01:37

子查詢-巢狀查詢

子查詢是指一個查詢語句巢狀在另一個語句內部的查詢

原始查詢方法

SELECT last_name,salaryFROM employeesWHERE last_name='Abel';SELECT last_name,salaryFROM employeesWHERE salary>11000;

自連線

SELECT e2.last_name,e2.salaryFROM employees e1,employees e2WHERE e2.salary>e1.salary AND e1.last_name='Abel';

子查詢

SELECT last_name,salaryFROM employeesWHERE salary>(SELECT salaryFROM employeesWHERE last_name='Abel');

稱謂的規範：外查詢（或主查詢），內查詢

子查詢（內查詢）在主查詢之前一次執行完成。
子查詢的結果被主查詢（外查詢）使用。

注意事項

子查詢要包含在括號內
將子查詢放在比較條件的右側
單行操作符對應單行子查詢，多行操作符對應多行子查詢

子查詢分類

單行子查詢 vs 多行子查詢

按內查詢的結果返回一條還是多條記錄，將子查詢分為單行子查詢和多行子查詢

相關子查詢vs不相關子查詢

按照內查詢是否被執行多次進行劃分
相關子查詢需求：查詢工資大於本部分平均工資的員工資訊
不相關子查詢需求：查詢工資大於本公司平均工資的員工資訊

單行子查詢

單行比較運運算元

操作符	含義
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

子查詢的編寫思路

從裡往外寫
從外往裡寫

SELECT * 
FROM employees
WHERE salary>(
		SELECT salary
		FROM employees
		WHERE employee_id=149
);
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
);
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
									SELECT manager_id
									FROM employees
									WHERE employee_id=141
)
AND 
department_id=(
									SELECT department_id
									FROM employees
									WHERE employee_id=141
)
AND
employee_id<>141;
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
									SELECT manager_id,department_id
									FROM employees
									WHERE employee_id=141
)
AND 
employee_id<>141;

HAVING中的子查詢

先執行子查詢
向主查詢中的HAVING字句返回結果

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

CASE中的子查詢

在CASE表示式中使用單列子查詢

SELECT employee_id,last_name ,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800) THEN 'Canada'																								ELSE 'USA' END 'location'
FROM employees;

子查詢中的空值問題

若內查詢查詢結果為空的，則不會報錯，子查詢不會返回任何行

非法使用子查詢

多行子查詢

多行子查詢也被稱為集合比較子查詢

內查詢返回多行資料

使用多行比較操作符

多行比較操作符

操作符	含義
IN	等於列表中的任意一個
ANY	需要和單行比較操作符一起使用，和子查詢返回的某一個值比較（任一）
ALL	需要和單行比較操作符一起使用，和子查詢返回的所有值比較
SOME	實際上是ANY的別名，作用相同，一般常使用ANY

MySQL中聚合函數是不能巢狀的

SELECT employee_id,last_name
FROM employees 
WHERE salary IN
(
	SELECT MIN(salary)
	FROM employees
	GROUP BY department_id
);
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary < ANY (
	SELECT salary 
	FROM employees
	WHERE job_id='IT_PROG'
);
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary < ALL (
	SELECT salary 
	FROM employees
	WHERE job_id='IT_PROG'
);
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
	SELECT AVG(salary) avgsal
	FROM employees
	GROUP BY department_id
);
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=
(
SELECT MIN(avgsal)
FROM (
SELECT AVG(salary) avgsal
FROM employees
GROUP BY department_id
) dbsal
);

多行子查詢空值問題

子查詢中存在空值的情況將使得查詢結果生成的位空