SQL 三大連接查詢：內連接、外連接、交叉連接深度解析

連接分類總覽 (SQL-92 標準)

FROM join_table join_type join_table [ON (join_condition)]
join_type:
  INNER JOIN          -- 內連接
  LEFT  OUTER JOIN    -- 左外連接
  RIGHT OUTER JOIN    -- 右外連接
  FULL  OUTER JOIN    -- 全外連接
  CROSS JOIN          -- 交叉連接 (笛卡兒積)

1. 內連接 (INNER JOIN)

• 僅回傳雙方皆符合 ON 條件的列。
• 三種子類型：
  • 等值連接：ON a.col = b.col，保留重複欄位。
  • 自然連接：等值連接 + SELECT 明確列出欄位，移除重複欄位。
  • 不等連接：>, >=, <, <=, <>, !>, !<。

-- 等值連接：同城市作者與出版社
SELECT * FROM authors a INNER JOIN publishers p ON a.city = p.city;

-- 自然連接：移除重複 city, state
SELECT a.*, p.pub_id, p.pub_name, p.country
FROM authors a INNER JOIN publishers p ON a.city = p.city;

2. 外連接 (OUTER JOIN)

-- 左外連接：論壇文章 + 作者 (無作者仍顯示文章)
SELECT a.*, b.* FROM luntan a LEFT JOIN usertable b ON a.username = b.username;

-- 全外連接：所有城市 + 所有作者
SELECT a.*, b.* FROM city a FULL OUTER JOIN user b ON a.username = b.username;

3. 交叉連接 (CROSS JOIN)

• 無 ON 條件，回傳笛卡兒積：列數 = 左表列數 × 右表列數。
• 例：6 類圖書 × 8 家出版社 = 48 列。

SELECT type, pub_name FROM titles CROSS JOIN publishers ORDER BY type;

特殊限制

• text / ntext / image 欄位不可直接 JOIN，需轉換或用長度比較繞過：DATALENGTH(col1) = DATALENGTH(col2)。

自連接

表與自身連接，需用別名區分：

SELECT e1.name AS Employee, e2.name AS Manager
FROM emp e1 LEFT JOIN emp e2 ON e1.mgr_id = e2.emp_id;

最佳實務建議

• 優先使用 LEFT JOIN，由主表延伸，閱讀性最佳。
• ON 子句只放連接條件，過濾條件放 WHERE。
• 避免隱含舊式 WHERE a.id = b.id 寫法，改用明確 JOIN 語法。