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

folks.

I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries.

I was reading the following article that explains how table JOIN works:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

However, I am stuck at the FULL OUTER JOIN example (comprised of tableA and tableB):

tableA:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tablea;

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Pirate    |
|  2 | Monkey    |
|  3 | Ninja     |
|  4 | Spaghetti |
+----+-----------+

tableB:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tab

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Rutabanga   |
|  2 | Pirate      |
|  3 | Darth Vader |
|  4 | Ninja       |
+----+-------------+

I am trying the same as proposed in the website:

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

and receive error 1064.

Does anyone have an idea what the reason could be?

Thank in advance for the assistance.

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.)

StackExchange Database Administrators Q#55498, answer score: 8