(SQLite)FULL OUTER JOINの代用方法

以前,(SQL)Access(JET)で FULL OUTER JOIN を行うで、FULL OUTER JOINをサポートしていないAccessで完全外部結合を行う方法を書きました。

SQLiteもFULL OUTER JOINがサポートされていないので、完全外部結合を行う場合同様の方法を用いてやる必要があります。
以前の記事の参考リンクとさせてもらったMicrosoft Accessで完全外部結合をするの1一つ目の方法は、LEFT OUTER JOIN と RIGHT OUTER JOIN で結合したテーブルを UNION するという方法ですが、SQLiteには実は RIGHT OUTER JOIN が使えません。 なので、LEFT OUTER JOIN と UNION だけで実現してやらないといけません。

さて、以下のような a と b というテーブルがあるとします。
テーブル a

id	value
1	hoge1
2	hoge2

テーブル b

id	value
2	piyo1
3	piyo2

SQL Server 2008で FULL OUTER JOIN をかけると以下の様な結果になります。

クエリ文：

SELECT a.id , a.value , b.id AS Expr1, b.value AS Expr2
FROM   a
       FULL OUTER JOIN b
       ON     a.id = b.id

結果：

id	value	Expr1	Expr2
1	hoge01	NULL	NULL
2	hoge02	2	piyo01
NULL	NULL	3	piyo02

同じ結果を SQLite (Accessでも可)で得たい時は以下のようなクエリとします。

SELECT a.id , a.value , b.id , b.value
FROM   a
       LEFT OUTER JOIN b
       ON     a.id = b.id
UNION
SELECT a.id , a.value , b.id , b.value
FROM   b
       LEFT OUTER JOIN a
       ON     a.id = b.id
WHERE  a.id ISNULL

結果：

a.id	a.value	b.id	b.value
1	hoge01	NULL	NULL
2	hoge02	2	piyo01
NULL	NULL	3	piyo02

要はaからbへは左外部結合をし、bからaへの左外部結合で結合条件となる値がnullのもの(つまり、bにあってaに無いレコード)を UNION で統合してるわけですね。

さて、大抵の場合は上記の a.id と b.id の値をまとめて以下の様な表にしたいという場合が多いと思います。

id	a.value	b.value
1	hoge1	NULL
2	hoge2	piyo1
3	NULL	piyo2

その場合は、以下の様なクエリにします。

SELECT id_all.id , a.value , b.value
FROM   ( SELECT a.id
       FROM  a
       UNION
       SELECT b.id
       FROM  b
       ) AS id_all
       LEFT OUTER JOIN a
       ON  id_all.id = a.id
       LEFT OUTER JOIN b
       ON  id_all.id = b.id