MySQL not recognizing column name prefixed with table name-CodePudding

I am trying to reference a column name by prefixing it with the table name using the following query:

SELECT Employees.employee_id
FROM Employees
FULL JOIN Salaries on Employees.employee_id = Salaries.employee_id

I am getting an error that says "Unknown column 'Employees.employee_id' in 'field list'" even though employee_id is a column in Employees. The full table schema is:

Table: Employees | Column Name | Type | |:------------|:--------| | employee_id | int | | name | varchar | employee_id is the primary key for this table. Each row of this table indicates the name of the employee whose ID is employee_id.

Table: Salaries | Column Name | Type | |:------------|:--------| | employee_id | int | | salary | int | employee_id is the primary key for this table. Each row of this table indicates the salary of the employee whose ID is employee_id.

Why is it giving this error and how do I fix this?

CodePudding user response：

Try using LEFT JOIN or RIGHT JOIN instead of a FULL JOIN

CodePudding user response：

The error is misleading and caused by FULL JOIN , full join does not exist in mysql see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8805879fb5cbe99514e65be464980a18. I am not going to suggest a fix since you haven't said what you are trying to achieve but if you search for mysql full join there are lots of suggestions on this site.