核心概念速覽

連接類型	結果集包含	口訣
INNER JOIN	雙方**都符合**條件的列	只取交集
LEFT JOIN	左表**全部** + 右表匹配列 (無匹配則 NULL)	左為主
RIGHT JOIN	右表**全部** + 左表匹配列 (無匹配則 NULL)	右為主

基礎範例

表 A：

aid	adate
1	a1
2	a2
3	a3

表 B：

bid	bdate
1	b1
2	b2
4	b4

INNER JOIN

SELECT * FROM A INNER JOIN B ON A.aid = B.bid;

aid	adate	bid	bdate
1	a1	1	b1
2	a2	2	b2

LEFT JOIN

SELECT * FROM A LEFT JOIN B ON A.aid = B.bid;

aid	adate	bid	bdate
1	a1	1	b1
2	a2	2	b2
3	a3	NULL	NULL

RIGHT JOIN

SELECT * FROM A RIGHT JOIN B ON A.aid = B.bid;

aid	adate	bid	bdate
1	a1	1	b1
2	a2	2	b2
NULL	NULL	4	b4

進階實戰：多表連接與資料同步

多表 LEFT JOIN 串接

SELECT a.*, b.*, c.*
FROM (article a LEFT JOIN class b ON a.classid = b.classid)
     LEFT JOIN nclass c ON a.nclassid = c.nclassid;

同步兩表資料 (UPDATE + JOIN)

UPDATE article a
INNER JOIN tag t ON a.articleid = t.articleid
SET t.classid = a.classid, t.nclassid = a.nclassid;

找出主表有但從表無的資料 (INSERT ... LEFT JOIN ... WHERE IS NULL)

INSERT INTO tag (articleid, classid, nclassid)
SELECT a.articleid, a.classid, a.nclassid
FROM article a LEFT JOIN tag t ON a.articleid = t.articleid
WHERE t.articleid IS NULL;

複雜巢狀 JOIN 寫入標籤

INSERT INTO tag (articleid, classid, nclassid, tag)
SELECT a.articleid, a.classid, a.nclassid, d.class + ' ' + c.nclass
FROM article a
LEFT JOIN (nclass c LEFT JOIN class d ON c.classid = d.classid)
     ON a.classid = c.classid AND a.nclassid = c.nclassid
LEFT JOIN tag b ON a.articleid = b.articleid
WHERE a.classid = 4 AND a.nclassid = 154;

效能提示

• 確保 ON 條件欄位有索引。
• LEFT JOIN 左表大、右表小時效能佳；反之考慮改寫為 RIGHT JOIN 或交換順序。
• 避免在 ON 條件中使用函數或運算。
• 僅選取需要的欄位，避免 SELECT *。