連接查詢將多張表按條件組合成虛擬大表，再進行篩選投影。分類依據連接條件與結果集保留邏輯：
CREATE TABLE teacher (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), gender ENUM('male','female','secret')) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO teacher VALUES (NULL,'奧巴馬','male'),(NULL,'楊冪','female'),(NULL,'rose','secret'),(NULL,'阿黛爾','female');
CREATE TABLE grade (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), room VARCHAR(3)) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO grade VALUES (NULL,'bigdata','203'),(NULL,'english','207'),(NULL,'maths','308'),(NULL,'PE','228');
CREATE TABLE teacher_grade (id INT PRIMARY KEY AUTO_INCREMENT, t_id INT, c_id INT, day TINYINT, begin_date DATE, end_date DATE) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO teacher_grade VALUES
(NULL,1,1,15,'2015-01-15','2015-01-30'),
(NULL,1,2,18,'2015-02-10','2015-02-28'),
(NULL,1,3,22,'2015-03-01','2015-03-23'),
(NULL,2,1,20,'2015-03-05','2015-03-25'),
(NULL,2,2,22,'2015-04-08','2015-04-30'),
(NULL,3,1,15,'2015-05-01','2015-05-16'),
(NULL,1,1,15,'2015-05-05','2015-05-20'),
(NULL,3,3,15,'2015-07-01','2015-07-16'),
(NULL,2,1,5,'2015-06-17','2015-06-23');僅返回雙方均滿足條件的行。語法：tbl_left INNER JOIN tbl_right ON 條件。
-- 推薦寫法：ON 放連接條件，WHERE 放過濾條件
SELECT t.name, tg.day, tg.begin_date
FROM teacher AS t
INNER JOIN teacher_grade AS tg ON t.id = tg.t_id
WHERE tg.day >= 20;| 寫法 | 示例 | 特點 |
|---|---|---|
| ON | ON t.id = tg.t_id | 通用，連接時過濾，推薦 |
| WHERE | WHERE t.id = tg.t_id | 笛卡爾積後過濾，內連接等價但語義不清 |
| USING | USING (id) | 要求同名列，結果集自動去重該列 |
SELECT * FROM teacher INNER JOIN teacher_grade; -- 4×9=36 行
SELECT * FROM teacher CROSS JOIN teacher_grade; -- 同義保留一方不滿足條件的行，另一方用 NULL 填充。MySQL 不支持 FULL OUTER JOIN，可用 UNION 模擬。
SELECT t.id AS teacher_id, tg.id AS teacher_grade_id, t.name, tg.day, tg.begin_date
FROM teacher AS t
LEFT OUTER JOIN teacher_grade AS tg ON t.id = tg.t_id;左表所有行保留，右表無匹配則 NULL。
SELECT t.id AS teacher_id, tg.id AS teacher_grade_id, t.name, tg.day, tg.begin_date
FROM teacher AS t
RIGHT OUTER JOIN teacher_grade AS tg ON t.id = tg.t_id;右表所有行保留，左表無匹配則 NULL。
(SELECT ... FROM teacher LEFT JOIN teacher_grade ON ...)
UNION
(SELECT ... FROM teacher RIGHT JOIN teacher_grade ON ...);外連接中 WHERE 會過濾掉 NULL 行，破壞外連接語義。連接條件必須寫在 ON 或 USING 中。
MySQL 自動根據同名列連接，無需指定條件。分為 NATURAL JOIN（內）、NATURAL LEFT JOIN、NATURAL RIGHT JOIN。
SELECT * FROM one NATURAL JOIN two;
-- 等價於
SELECT * FROM one INNER JOIN two USING (public_field);SELECT s.*, si.*
FROM info_class AS c
LEFT JOIN info_student AS s ON c.id = s.class_id
LEFT JOIN info_student_info AS si ON s.id = si.id
WHERE c.class_name = '001';SELECT m.*, h.class_name AS host, g.class_name AS guest
FROM match AS m
LEFT JOIN class AS h ON m.host_id = h.id
LEFT JOIN class AS g ON m.guest_id = g.id;t、tg），提升可讀性；t.id AS teacher_id），前端映射友好；EXPLAIN 檢查 type=ref/eq_ref、rows 預估；SELECT *，僅選取所需列減少網絡與內存開銷；STRAIGHT_JOIN 固定驅動順序，或調整 join_buffer_size。
