Full outer join does not work for two small tables, error 1064

The simple reason is that MySQL has not implemented FULL outer joins, only LEFT and RIGHT ones.

You can simulate the FULL join with a UNION of a LEFT and a RIGHT outer join:

SELECT TableA.*, TableB.* 
FROM 
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION 

SELECT TableA.*, TableB.*
FROM 
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name ;

or (for improved performance) using UNION ALL:

SELECT TableA.*, TableB.* 
FROM 
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION ALL

SELECT TableA.*, TableB.*
FROM 
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name
WHERE 
    TableA.name IS NULL ;

Both queries (UNION and UNION ALL) will return same results. Unless the result of the join is not unique (if it produces 2 or more identical rows). Since this question has SELECT *, so SELECT a.*, b.*, for this to happen, the tables should have duplicate rows, which is rather unlikely (a table with no primary or unique constraint.)


In MySql doesn't exists FULL OUTER JOIN keyword

You can try this:

SELECT * FROM TableA A
LEFT JOIN TableB B ON A.name = B.name
UNION
SELECT * FROM TableA A
RIGHT JOIN TableB B ON A.name = B.name

Tags:

Mysql

Join