Search code examples
mysqldelimiterouter-join

Unknown column '..' in 'field list' join

I made a simple query with join but get always this annoying error.
The query:

SELECT 
    `verk.id`, `verk.date`, `verk.quant`, `verk.verid`, `verk.kunde`, `verk.gebracht`, `loginuser_aqa.name`, `loginuser_aqa.id`
FROM 
    `verk` 
FULL JOIN 
    `loginuser_aqa`
ON
    loginuser_aqa.id = verk.verid
WHERE
    verk.gebracht = 0
ORDER BY  verk.date;



The error:

Unknown column 'verk.id' in 'field list'


Provided a demo on rextester:
http://rextester.com/HDTJAA39589

I already tried to leave "verk" from verk.id away, but then i get another error:

id is ambiguous..

Solution

  • You have at least two problems in this query.

    First, when you use back-ticks to delimit identifiers, you must delimit the table alias separately from the column name.

    `verk.id`   -- WRONG

`verk`.`id` -- CORRECT

    The reason is that SQL actually allows you to define column names containing punctuation, white space, etc. if you delimit the column names. So that's what you appear to be doing, requesting a column named verk.id

    verk.id     -- ALSO CORRECT

    As others have commented, you don't usually need to use delimited identifiers at all. Use them if your identifiers conflict with MySQL Reserved Words, or if you need to use punctuation, whitespace, or international characters.

    The second problem is the MySQL doesn't support FULL JOIN. It doesn't even recognize FULL as an SQL keyword. So your query formed like this:

    ...
FROM 
    `verk` 
FULL JOIN 
    `loginuser_aqa`
ON ...

    Is interpreted by MySQL as if you had done this:

    ...
FROM 
    `verk` AS `FULL`
JOIN 
    `loginuser_aqa`
ON ...

    In other words, since AS is an optional keyword in SQL, you have just set FULL as the table alias for verk.

    When you define a table alias, you must use the table alias for any column belonging to that table. You can no longer reference columns as verk.id, you must use FULL.id. This part is standard SQL behavior, not a MySQL bug.

    Another problem with this is that you aren't getting a FULL OUTER JOIN in your query result, you're just getting a plain JOIN which is a synonym for INNER JOIN. There's no error, but if you needed a full outer join, you won't get the results you expect.

    I reported the issue of MySQL not supporting FULL as a reserved word in 2013: https://bugs.mysql.com/bug.php?id=69858 You can add your vote to the priority of the bug if you want by logging in and clicking the "Affects me" button on that page.

    If you need to do a FULL OUTER JOIN in MySQL, you must use a workaround. See example here: Full Outer Join in MySQL