MySQL 關聯查詢三種寫法對比：ON vs USING vs WHERE 條件

三種語法形式

-- 1. ANSI ON 標準寫法
SELECT * FROM film 
JOIN film_actor ON (film.film_id = film_actor.film_id);

-- 2. ANSI USING 簡潔寫法（同名欄位）
SELECT * FROM film 
JOIN film_actor USING (film_id);

-- 3. Theta 風格（舊式 WHERE 連接）
SELECT * FROM film, film_actor 
WHERE film.film_id = film_actor.film_id;

核心差異對比

實戰驗證：結果集結構差異

-- USING：film_id 僅出現一次
SELECT * FROM film JOIN film_actor USING (film_id) LIMIT 1\G
*************************** 1. row ***************************
film_id: 96
title: BREAKING HOME
...
actor_id: 17
last_update: 2006-02-15 05:05:03

-- ON：film_id 出現兩次
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) LIMIT 1\G
*************************** 1. row ***************************
film_id: 96
title: BREAKING HOME
...
actor_id: 17
film_id: 96   -- 重複！
last_update: 2006-02-15 05:05:03

引用關聯欄位的陷阱

-- USING：可直接引用，無歧義
SELECT film.title, film_id FROM film JOIN film_actor USING (film_id);  -- ✅ OK

-- ON：必須指定表名，否則報錯
SELECT film.title, film_id FROM film JOIN film_actor ON (film.film_id = film_actor.film_id);
-- ERROR 1052 (23000): Column 'film_id' in field list is ambiguous

執行計畫：底層其實都一樣

EXPLAIN EXTENDED SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17\G
SHOW WARNINGS\G
-- Message: select ... from `film` join `film_actor` where ( (`film`.`film_id` = `film_actor`.`film_id`) and ... )

MySQL 優化器會將所有寫法統一轉換為 Theta 風格執行，**效能完全相同**。

最佳實踐建議

1. 首選 ANSI ON：標準、通用、條件分離清晰，團隊協作無歧義
2. 同名欄位可用 USING：簡潔、結果集無重複欄位，適合快速查詢與報表
3. 禁用 Theta 風格：新專案不應使用，維護成本高、易出錯
4. 複雜多表 JOIN：建議使用 CTE (MySQL 8.0+) 分解邏輯

進階：多表 JOIN 範例

-- 推薦寫法：層次清晰
SELECT f.title, a.first_name, a.last_name, c.name AS category
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE f.length > 120 AND a.actor_id = 17;