1054 Unknown Column in Full Join, but not other Joins

Question

Why do I get a "1054 Unknown Column error" from the following mysql syntax:

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;

It results in error:

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

I only get this error when attempting a FULL JOIN. All other joins (Inner, Right, Left) are successfull when using this exact syntax. (I have tried the FULL OUTER JOIN variation).

Answer 1

Are reported here, MySQL does not support FULL JOIN, so your query is interpreted as follows:

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;

Now, since it has FULL as table alias for jos_legalally_attorneys, it doesn't work properly.

If you try the following query, it should give no error (even though it doesn't do what you want):

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;

Now, in order to get the FULL JOIN you need under MySQL, you need to do something like this (as shown 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