真實業務中,資料分散在多張關聯表中。本文以員工表 mytable 與文章表 title 為例,循序漸進講解多表查詢語法、連接類型與別名技巧。
mytable+----------+------+------------+-----------+
| name | sex | birth | birthaddr |
+----------+------+------------+-----------+
| abccs | f | 1977-07-07 | china |
| mary | f | 1978-12-12 | usa |
| tom | m | 1970-09-02 | usa |
+----------+------+------------+-----------+
titleCREATE TABLE title (
writer VARCHAR(20) NOT NULL,
title VARCHAR(40) NOT NULL,
senddate DATE
);
+--------+-------+------------+
| writer | title | senddate |
+--------+-------+------------+
| abccs | a1 | 2000-01-23 |
| mary | b1 | 1998-03-21 |
| abccs | a2 | 2000-12-04 |
| tom | c1 | 1992-05-16 |
| tom | c2 | 1999-12-12 |
+--------+-------+------------+
SELECT name, sex, title
FROM mytable, title
WHERE name = writer AND name = 'abccs';
結果:
+-------+------+-------+
| name | sex | title |
+-------+------+-------+
| abccs | f | a1 |
| abccs | f | a2 |
+-------+------+-------+
缺點:WHERE 混雜關聯條件與過濾條件,可讀性差,易漏寫關聯條件導致笛卡兒積。
SELECT m.name, m.sex, t.title
FROM mytable m
INNER JOIN title t ON m.name = t.writer
WHERE m.name = 'abccs';
-- 查詢所有員工及其文章(沒發文也要列出)
SELECT m.name, m.sex, t.title
FROM mytable m
LEFT JOIN title t ON m.name = t.writer;
結果包含 mary(有文章)與 tom(有文章),若有員工從未發文,title 顯示 NULL。
RIGHT JOIN:保留右表所有列(較少用,可改寫 LEFT JOIN)。LEFT JOIN UNION RIGHT JOIN 模擬。若兩表關聯欄位同名(如皆為 name),必須加表前綴或別名區分:
SELECT m.name, m.sex, t.title
FROM mytable m
JOIN title t ON m.name = t.name -- 這裡 t.name 指 title 表的 name 欄位
WHERE m.name = 'abccs';
最佳實務:永遠給表起別名(m、t),所有欄位引用均加前綴 m.name、t.title,消除歧義、提升可讀性。
mytable.name、title.writer 加索引,否則大表 JOIN 會全表掃描。STRAIGHT_JOIN 強制順序。SELECT * 傳輸無用資料、佔用網路與記憶體。type(ref/eq_ref 佳)、rows(預估掃描列數)、Extra(Using where/Using index)。-- 多表 UPDATE(同步文章作者性別到文章表冗余欄位)
UPDATE title t
INNER JOIN mytable m ON t.writer = m.name
SET t.author_sex = m.sex;
-- 多表 DELETE(刪除從未發文的員工記錄)
DELETE m
FROM mytable m
LEFT JOIN title t ON m.name = t.writer
WHERE t.writer IS NULL;