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