多表查詢是關聯式資料庫威力所在。本文系統整理 INNER JOIN、LEFT/RIGHT/FULL OUTER JOIN、CROSS JOIN、自我聯結 語法與實戰範例，並說明 WHERE 舊式聯結與 JOIN ... ON 標準語法的差異。
| 聯結類型 | 關鍵字 | 結果集包含 | 適用場景 |
|---|---|---|---|
| 內聯結 | INNER JOIN / JOIN | 雙方皆符合條件的列 | 主流查詢：訂單+明細、員工+部門 |
| 左外聯結 | LEFT [OUTER] JOIN | 左表所有列 + 右表匹配列 (無匹配為 NULL) | 報表需保留主表：所有客戶+訂單(含無訂單) |
| 右外聯結 | RIGHT [OUTER] JOIN | 右表所有列 + 左表匹配列 | 較少用，可改 LEFT JOIN 交換表順序 |
| 完整外聯結 | FULL [OUTER] JOIN | 雙方所有列，無匹配處為 NULL | 雙向對帳：兩系統資料比對 |
| 交叉聯結 | CROSS JOIN | 笛卡兒積：左表列數 × 右表列數 | 產生完整維度組合 (見 CROSS JOIN 專文) |
SELECT select_list
FROM table_a a
INNER JOIN table_b b ON a.common_col = b.common_col
LEFT JOIN table_c c ON b.other_col = c.other_col
WHERE a.filter_col = 'value';ON，篩選條件放在 WHERE，語意清晰、優化器易處理。a.id)。SELECT a.col1, b.col2
FROM table_a a, table_b b
WHERE a.id = b.id
AND a.status = 1;(+) (Oracle) 或 *= (SQL Server 舊版)，已淘汰、不可攜、易出錯。*= =* 支援。SELECT
m.MovieID, m.MovieName, m.MovieType, m.Brief,
d.DiscID, d.DiscState, d.DiscType, d.Remarks
FROM Movies m
INNER JOIN Discs d ON m.MovieID = d.MovieID
WHERE m.MovieType = N'動作';SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CompanyName;SELECT e.EmpName AS Employee, m.EmpName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmpID
ORDER BY Manager, Employee;SELECT
c.CompanyName,
e.EmpName,
d.DeptName,
s.Grade, s.MinSalary, s.MaxSalary
FROM Companies c
INNER JOIN Employees e ON c.CompanyID = e.CompanyID
INNER JOIN Departments d ON e.DeptID = d.DeptID
INNER JOIN SalaryGrades s ON e.Salary BETWEEN s.MinSalary AND s.MaxSalary
WHERE c.Country = N'Taiwan';SELECT
cat.CategoryName,
COUNT(p.ProductID) AS ProductCount,
SUM(p.UnitsInStock) AS TotalStock
FROM Categories cat
LEFT JOIN Products p ON cat.CategoryID = p.CategoryID
GROUP BY cat.CategoryName
ORDER BY TotalStock DESC;ON UPPER(a.col) = UPPER(b.col) 導致索引失效，改用計算欄位/持久化計算欄位。SELECT * 增加網路傳輸、記憶體、暫存表壓力。| ANSI | Oracle (+) |
|---|---|
LEFT JOIN b ON a.id = b.id | a.id = b.id(+) |
RIGHT JOIN b ON a.id = b.id | a.id(+) = b.id |
FULL JOIN | 不支援 (需 UNION 兩邊外聯結) |
掌握標準 JOIN ... ON 語法，能撰寫可攜、可讀、高效能的多表查詢，是 SQL 開發者必備核心技能。
