Access (Jet/ACE) SQL 引擎對 LEFT JOIN 有獨特括號要求:每個 JOIN 必須用括號包裹,多條件 ON 子句亦需加括號。不遵守即拋出「參數未指定值」或「語法錯誤」。本文提供標準寫法範本與常見錯誤對照。
SELECT * FROM (A LEFT JOIN B ON A.key = B.key)SELECT * FROM ((A LEFT JOIN B ON ...) LEFT JOIN C ON ...)ON (B.key=C.key AND B.type=C.type)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);
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 JOIN | Access 不支援 | 交換表順序改用 LEFT JOIN |
qry_AB,再 SELECT * FROM (qry_AB LEFT JOIN C ON ...),降低複雜度。WHERE 條件替代部分 JOIN (僅限內聯結等值) 簡化結構。遵循「每 JOIN 一對括號、ON 多條件加括號」鐵律,Access 多表聯結即可穩定執行。