4

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.

0
7

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
0
8

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

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.