萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Access 教學 >> Access 基礎教學

Access 多表 LEFT JOIN 語法規範:括號嵌套與多條件 ON 寫法詳解

Access (Jet/ACE) SQL 引擎對 LEFT JOIN 有獨特括號要求:每個 JOIN 必須用括號包裹,多條件 ON 子句亦需加括號。不遵守即拋出「參數未指定值」或「語法錯誤」。本文提供標準寫法範本與常見錯誤對照。

核心規則

  1. 單一 JOIN:SELECT * FROM (A LEFT JOIN B ON A.key = B.key)
  2. 多重 JOIN:由內而外層層括號 SELECT * FROM ((A LEFT JOIN B ON ...) LEFT JOIN C ON ...)
  3. ON 多條件:ON (B.key=C.key AND B.type=C.type)
  4. 別名建議:簡短別名減少拼寫錯誤。

標準範本

兩表聯結

SELECT A.*, B.Col1, B.Col2
FROM (TableA AS A LEFT JOIN TableB AS B ON A.ID = B.ID)
WHERE A.Status = 'Active';

三表聯結 (標準巢狀)

SELECT A.ID, A.Name, B.Detail, C.Extra
FROM ((TableA AS A LEFT JOIN TableB AS B ON A.ID = B.A_ID)
      LEFT JOIN TableC AS C ON B.ID = C.B_ID)
WHERE A.Category = 'X';

四表聯結 (實務常見)

SELECT *
FROM ((((URL AS A
    LEFT JOIN MobileAfter AS B ON A.URL = B.URL)
    LEFT JOIN MobileBefore AS C ON C.URL = A.URL)
    LEFT JOIN TeleCOM AS D ON D.URL = A.URL)
    LEFT JOIN UniCOM AS E ON E.URL = A.URL);

多條件 ON 子句

SELECT *
FROM (A LEFT JOIN B ON A.ID = B.ID)
LEFT JOIN C ON (B.ID = C.ID AND B.Code = C.Code AND B.Year = C.Year);

常見錯誤對照

錯誤寫法問題正確寫法
FROM A LEFT JOIN B ON ... LEFT JOIN C ON ...缺少外層括號FROM (A LEFT JOIN B ON ...) LEFT JOIN C ON ...
ON B.ID=C.ID AND B.Code=C.Code多條件無括號ON (B.ID=C.ID AND B.Code=C.Code)
RIGHT JOINAccess 不支援交換表順序改用 LEFT JOIN

調試技巧

  1. 在 Access 查詢設計檢視建立聯結 → 切換 SQL 檢視 → 複製生成的標準括號結構。
  2. 分步驟建立:先建兩表查詢存為 qry_AB,再 SELECT * FROM (qry_AB LEFT JOIN C ON ...),降低複雜度。
  3. 使用 WHERE 條件替代部分 JOIN (僅限內聯結等值) 簡化結構。

遵循「每 JOIN 一對括號、ON 多條件加括號」鐵律,Access 多表聯結即可穩定執行。

copyright © 萬盛學電腦網 all rights reserved