添加LEFT JOIN至SELECT-FROM-WHERE语句时出现SQL语法错误

问题描述 #

现有inscription表和diver表，单表查询语句可正常执行：

SELECT id_membre, id_sortie, id_diver, licence, mail, actif
FROM inscription
WHERE id_sortie = 4071

但添加LEFT JOIN后语句触发1064语法错误，报错语句：

SELECT id_membre, id_sortie, id_diver, licence, mail, actif
FROM inscription
WHERE id_sortie = 4071
LEFT JOIN diver ON inscription.licence = diver.licence;

错误信息：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN diver ON inscription.licence = diver.licence LIMIT 0, 30' at line 3

错误原因 #

• SQL语法顺序违规：LEFT JOIN属于表连接逻辑，必须放在FROM子句之后、WHERE子句之前。原语句将筛选条件WHERE id_sortie = 4071放在了LEFT JOIN前面，打破了SQL的语法执行顺序，数据库无法正确解析语句结构。

解决方法 #

调整语句顺序，将LEFT JOIN移至FROM之后、WHERE之前；若两表均包含licence字段，建议在SELECT中指定表名前缀以避免字段歧义：

SELECT id_membre, id_sortie, id_diver, inscription.licence, mail, actif
FROM inscription
LEFT JOIN diver ON inscription.licence = diver.licence
WHERE id_sortie = 4071;

内容的提问来源于stack exchange，提问作者zebu14