mysql中union连接,MySQL为内部联接UNION右联接创建表

In my SQL 5.5:

Trying to create a table which is a result of a INNER JOIN of table A & B, and (UNION) RIGHT JOIN of table C & B.

CREATE TABLE IF NOT EXISTS TABLE_NAME AS (

(SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b

ON a.column1 = b.column1)

UNION

(SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b

ON b.column1 = c.column1)

);

Error:

ERROR 1064 (42000) at line 11: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT a.column1, b.column2 FROM TABLEA AS a

The other attempt:

CREATE TABLE IF NOT EXISTS TABLE_NAME AS (

(SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b

ON a.column1 = b.column1)

UNION

(SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b

ON b.column1 = c.column1)

);

Error:

ERROR 1064 (42000) at line 11: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

Could any guru offer some advice? Thanks.

解决方案

Try this

CREATE TABLE IF NOT EXISTS TABLE_NAME AS

(

SELECT S.*

FROM

(

SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b ON a.column1 = b.column1

UNION

SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b ON b.column1 = c.column1

) S

);