FULL OUTER JOIN generates errors

Question

FULL OUTER JOIN generates errors

Navigation

#1 by (3 votes)

3

I'm 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 for the right syntax to use near 'OUTER JOIN AccessDayClass ON AccessDayClass . DateOnly = 'AccessDayNotClass' at line 6

I used INNER JOIN and it worked correctly, the problem only occurs with FULL OUTER JOIN . Following is the structure of both tables.

Using MySQL 5.7.19

mysql join

asked by anonymous 19.06.2018 / 00:31

1 answer

CSS3 Fit table to fill in whitespace generated on the Landscape page Get data via JSON by URL in C #

score 3 · Accepted Answer

As demonstrated by in this OS response in English , MySQL does not support the FULL OUTER JOIN command.

As explained in the above response, you can emulate a FULL OUTER JOIN using UNION ALL of a LEFT JOIN and a RIGHT JOIN . So your query would look like:

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'

Remembering that UNION ALL will even keep records that will be duplicated by using JOIN . If you want to delete duplicates, use only UNION .