MySQL 多表關聯查詢完全指南：內連接、外連接、子查詢、UNION 彙總

MySQL 多表關聯查詢完全指南：內連接、外連接、子查詢、UNION 彙總

實務開發中頻繁需求：跨多張表查詢資料。本文系統整理 MySQL 連接查詢類型、語法、範例與子查詢替代方案，並分析效能差異。

一、關聯運算基礎

二、內連接查詢

語法 (ANSI 標準，推薦)

SELECT 欄位 FROM 表1 INNER JOIN 表2 ON 表1.欄位 = 表2.欄位;

分類與範例

1. 自連接 — 員工與主管

SELECT e.ename, e.job, m.ename AS manager
FROM t_employee e INNER JOIN t_employee m ON e.mgr = m.empno;

2. 等值連接 — 員工與部門

SELECT e.empno, e.ename, e.job, d.dname, d.loc
FROM t_employee e INNER JOIN t_dept d ON e.deptno = d.deptno;

3. 非等值連接 — 薪資區間、日期範圍

SELECT e.ename, e.job, m.ename
FROM t_employee e INNER JOIN t_employee m ON e.mgr = m.empno AND e.empno > m.empno;

三、外連接查詢

左外連接 (LEFT JOIN)

SELECT e.ename, e.job, m.ename AS manager
FROM t_employee e LEFT JOIN t_employee m ON e.mgr = m.empno;

結果包含無主管的員工 (KING)，manager 顯示 NULL。

右外連接 (RIGHT JOIN)

SELECT e.ename, e.job, m.ename
FROM t_employee e RIGHT JOIN t_employee m ON e.mgr = m.empno;

四、UNION 合併查詢

SELECT 欄位 FROM 表1 UNION SELECT 欄位 FROM 表2;      -- 去重
SELECT 欄位 FROM 表1 UNION ALL SELECT 欄位 FROM 表2;   -- 保留重複

五、子查詢 (推薦替代連接查詢)

優勢：避免笛卡爾積爆炸，大資料量效能優於 JOIN。

分類與範例

1. 單行單列 (標量子查詢) — 比較運算子

SELECT * FROM t_employee WHERE sal > (SELECT sal FROM t_employee WHERE ename='SMITH');

2. 單行多列 — 元組比較

SELECT ename, job, sal FROM t_employee
WHERE (sal, job) = (SELECT sal, job FROM t_employee WHERE ename='SMITH');

3. 多行單列 — IN / ANY / ALL / EXISTS

-- IN
SELECT * FROM t_employee WHERE deptno IN (SELECT deptno FROM t_dept);

-- ANY (> 任一 = > 最小)
SELECT ename, sal FROM t_employee WHERE sal > ANY (SELECT sal FROM t_employee WHERE job='MANAGER');

-- ALL (> 所有 = > 最大)
SELECT ename, sal FROM t_employee WHERE sal > ALL (SELECT sal FROM t_employee WHERE job='MANAGER');

-- EXISTS (相關子查詢，逐行檢查)
SELECT * FROM t_employee e WHERE EXISTS (SELECT 1 FROM t_dept d WHERE d.deptno = e.deptno);

4. 多行多列 (內嵌視圖/衍生表) — FROM 子句

-- 部門統計：連接查詢
SELECT d.deptno, d.dname, d.loc, COUNT(e.empno), AVG(e.sal)
FROM t_employee e INNER JOIN t_dept d ON e.deptno = d.deptno
GROUP BY d.deptno;

-- 子查詢版 (衍生表 employee)
SELECT d.deptno, d.dname, d.loc, emp.number, emp.average
FROM t_dept d INNER JOIN (
    SELECT deptno AS dno, COUNT(empno) AS number, AVG(sal) AS average
    FROM t_employee GROUP BY deptno
) emp ON d.deptno = emp.dno;

效能建議

• 大表關聯優先考慮子查詢或衍生表
• 確保 JOIN/WHERE 欄位有索引
• EXPLAIN 分析執行計畫，關注 type、rows、Extra
• 避免 SELECT *，只取需要欄位
• 適當使用 STRAIGHT_JOIN 強制連接順序