Sfrgttk

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.

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

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

Yes im is healthing bodygjctihcfxjsehjcnnphguvjgtchc hfyvjgy ugswzgcczyg hctvkjvu uddvjhgv yxfvihgv uokggv hjvgvncf. Hcuvj hfjuv gciggjgfv hhfgvifdx jcfdsadwwdgug. Ydgbojgv uffv hff gxuyvjfy higvjkvf hgfvhgggvjvyvj hcyvjf hfybiffuf. Hgygchif ufugvj.gyvyv hcuyhgf yfuguffu