php - 1054 完全连接中的未知列，但不是其他连接

Question

为什么我从以下 mysql 语法中得到“1054 未知列错误”:

SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
FULL JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id;

它导致错误:

#1054 - Unknown column 'jos_legalally_attorneys.user_id' in 'on clause' 

我只在尝试 FULL JOIN 时遇到此错误。使用此确切语法时，所有其他联接(内、右、左)都会成功。 (我已经尝试了 FULL OUTER JOIN 变体)。

Answer 1

被报道here ，MySQL不支持FULL JOIN，所以你的查询解释如下:

SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys as FULL #FULL is taken as table alias!!
INNER JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id;

现在，因为它有 FULL 作为 jos_legallally_attorneys 的表别名，它不能正常工作。

如果您尝试以下查询，它应该不会出错(即使它没有按照您的要求执行):

SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
FULL JOIN jos_legalally_attorney_education
ON FULL.user_id=jos_legalally_attorney_education.user_id;

现在，为了在 MySQL 下获得你需要的 FULL JOIN，你需要做这样的事情(如图 here 所示):

SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
LEFT JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id
UNION ALL
SELECT attorney_first_name, attorney_last_name, att_school, att_degree_obtained
FROM jos_legalally_attorneys
RIGHT JOIN jos_legalally_attorney_education
ON jos_legalally_attorneys.user_id=jos_legalally_attorney_education.user_id