關係運算基礎
| 運算 | 字段數 | 記錄數 | 說明 |
|---|
| UNION (並) | 兩表一致 | n1+n2-重複 | 合並同構表 |
| 笛卡爾積 | n1+n2 | n1×n2 | 無條件組合，極其危險 |
| 內連接 | n1+n2 | 匹配行 | 保留雙邊匹配行 |
| 左外連接 | n1+n2 | 左表全量+右匹配 | 左表為主 |
| 右外連接 | n1+n2 | 右表全量+左匹配 | 右表為主 |
| 全外連接 | n1+n2 | 雙邊全量 | MySQL 需 UNION 模擬 |
ANSI 92 連接語法（推薦）
-- 內連接
SELECT e.ename, d.dname FROM t_emp e INNER JOIN t_dept d ON e.deptno=d.deptno;
-- 自連接（查員工及領導）
SELECT e.ename, e.job, m.ename AS mgr FROM t_emp e INNER JOIN t_emp m ON e.mgr=m.empno;
-- 等值連接
SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.id;
-- 不等連接
SELECT * FROM t1 INNER JOIN t2 ON t1.id > t2.id;
-- 左外連接
SELECT e.ename, m.ename AS mgr FROM t_emp e LEFT JOIN t_emp m ON e.mgr=m.empno;
-- 右外連接
SELECT e.ename, m.ename AS mgr FROM t_emp e RIGHT JOIN t_emp m ON e.mgr=m.empno;
UNION 合並
SELECT id,name FROM t1 UNION SELECT id,name FROM t2; -- 去重
SELECT id,name FROM t1 UNION ALL SELECT id,name FROM t2; -- 保留重複
子查詢替代連接（核心優勢）
連接查詢先做笛卡爾積再過濾，大表易 OOM；子查詢可避免全量笛卡爾積，優化器更易下推條件。
-- 連接寫法
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM t_emp e INNER JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno;
-- 子查詢寫法（衍生表）
SELECT d.deptno,d.dname,d.loc,tmp.cnt,tmp.avg_sal
FROM t_dept d
INNER JOIN (
SELECT deptno, COUNT(empno) cnt, AVG(sal) avg_sal
FROM t_emp GROUP BY deptno
) tmp ON d.deptno=tmp.deptno;
子查詢分類與關鍵字
| 位置 | 返回 | 典型關鍵字 | 示例 |
|---|
| WHERE | 單行單列 | = > < != | sal > (SELECT sal FROM t WHERE ename='SMITH') |
| WHERE | 單行多列 | (col1,col2)=(...) | (sal,job)=(SELECT sal,job FROM t WHERE ename='SMITH') |
| WHERE | 多行單列 | IN / NOT IN / ANY / ALL / EXISTS | deptno IN (SELECT deptno FROM t_dept) |
| FROM | 多行多列 | —（衍生表） | 上文 GROUP BY 子查詢 |
ANY/ALL/EXISTS 精髓
> ANY：大於子查詢**最小值**即可。> ALL：大於子查詢**最大值**。EXISTS：相關子查詢，外表逐行驅動，內層返回行即為 TRUE，常用於半連接優化。
