LEFT JOIN とRIGHT JOINの合体版のような結合方法。

質問

LEFT JOIN とRIGHT JOINの合体版のような結合方法。

使用DBはMySQL5.0.45です。

２つのテーブルの結合方式で、結合条件に合致しなかった場合、
両方のデータを結果セットに含めることはできますか？
LEFT JOINだと、左側のテーブルのデータは結果に返ってきて、右側のデータはNULLになります。
RIGHT JOINだと、右側のテーブルのデータは結果に返ってきて、左側のデータはNULLになります。
これを「左側データあり右側NULL」「左側NULL右側データあり」というレコードを混ぜて結果として返したいです。

具体的には、

以下二つのテーブルがあるとします。

table : test1
value　　id
-------------------
ああ　　　3
いい　　　4
うう　 　 　5

table : test2
value　　id
--------------------
AAA　　　1
BBB　　　2
CCC　　　3
DDD　　　4

id を結合条件のキーとして、以下のような結果を得たいです。

NULL　　NULL　　AAA　　　1
NULL　　NULL　　BBB　　　2
ああ　　　3　 　　　CCC　　　3
いい　　　4　 　　　DDD　　　4
うう　 　　5　　　　NULL　　 NULL

LEFT JOINだと以下の結果に、

SELECT * FROM test1
LEFT JOIN test2 ON test1.id=test2.id

ああ　　　3　 　　　CCC　　　3
いい　　　4　 　　　DDD　　　4
うう　 　　5　　　　NULL　　 NULL

RIGHT JOINだと以下の結果になってしまいます。

SELECT * FROM test1
RIGHT JOIN test2 ON test1.id=test2.id

NULL　　NULL　　AAA　　　1
NULL　　NULL　　BBB　　　2
ああ　　　3　 　　　CCC　　　3
いい　　　4　 　　　DDD　　　4

以上、ご教示お願い致します。

回答No.2

guttten · ベストアンサー

先に回答してくださっている方のおっしゃる通り、MySQLでは完全外部結合
をサポートしていないようです。

蛇足かもしれませんが、完全外部結合はまさしく質問者様がやりたい事そのままの
ないようです。文法的には

SELECT * FROM test1
FULL OUTER JOIN test2 ON test1.id=test2.id

で、ORACLEなどでは質問者様の要望通りの結果が返ってきます。

先に回答して下さっている方の別解として、RIGHT JOINとLEFT JOINをUNIONする
というものもご紹介します。

質問者様の載せていた２つのSQLをUNIONするだけです。

SELECT * FROM test1
LEFT JOIN test2 ON test1.id=test2.id
UNION
SELECT * FROM test1
RIGHT JOIN test2 ON test1.id=test2.id

ご参考までに。

お礼

完全外部結合というのですね。
mysqlで無いということで今後に期待します。
ありがとうございます。

回答No.1

yambejp

私の記憶ではmysqlは完全外部結合をサポートしていないような・・・

ただしUNIONでidを拾って、LEFT JOINを２度することで対応は可能です。

SELECT t1.*,t2.*
FROM (SELECT id from test1 union SELECT id from test2) AS SUB
LEFT JOIN test1 as t1 using(id)
LEFT JOIN test2 as t2 using(id)
ORDER BY SUB.id

お礼

完全外部結合というのですね。
mysqlで無いということで今後に期待します。
ありがとうございます。