FULL OUTER JOIN generates errors

Asked

Viewed 1,298 times

3

I am trying to run the following Mysql script

SELECT 
`AccessDayClass`.`total` AS  'em aula', 
`AccessDayNotClass`.`total` AS  'em casa', 
`AccessDayClass`.`DateOnly` AS  'week' 
FROM `AccessDayNotClass` 
FULL OUTER JOIN `AccessDayClass` ON `AccessDayClass`.`DateOnly` =  `AccessDayNotClass`.`DateOnly`

And the server is responding with

#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 'OUTER JOIN AccessDayClass ON AccessDayClass.DateOnly = `Accessdaynotclass' at line 6

I used INNER JOIN and worked properly, the problem only occurs with the FULL OUTER JOIN. The following is the structure of both tables.

inserir a descrição da imagem aqui

I use Mysql 5.7.19

mysql join

  • Mysql does not have FULL JOINS, but maybe they can be emulated. 1) http://dev.mysql.com/doc/refman/5.0/en/outer-join-simplification.html 2) https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql

1 answer

3


As demonstrated in that OS reply in English, Mysql does not support command FULL OUTER JOIN.

As explained in the above-mentioned answer, you can emulate a FULL OUTER JOIN using the UNION ALL of a LEFT JOIN and a RIGHT JOIN. So your query would be:

SELECT 
 `AccessDayClass`.`total` AS  'em aula', 
 `AccessDayNotClass`.`total` AS  'em casa',
 `AccessDayClass`.`DateOnly` AS  'week' 
FROM `AccessDayClass`
LEFT JOIN `AccessDayNotClass` ON `AccessDayClass`.`DateOnly` =  `AccessDayNotClass`.`DateOnly`
UNION ALL
SELECT 
 `AccessDayClass`.`total` AS  'em aula', 
 `AccessDayNotClass`.`total` AS  'em casa',
 `AccessDayClass`.`DateOnly` AS  'week' 
FROM `AccessDayClass`
RIGHT JOIN `AccessDayNotClass` ON `AccessDayClass`.`DateOnly` =  `AccessDayNotClass`.`DateOnly`

Recalling that the UNION ALL will even keep records that will be duplicated by the use of JOIN. If you want to eliminate duplicates, use only UNION.

Browser other questions tagged mysql join

You are not signed in. Login or sign up in order to post.