OUTER JOIN

CROSS JOINを除くここまでの結合は、両テーブルの行の組み合わせのなかから、条件に合うものを選択していた。
よって、片方のテーブルのある行に対して、条件に合うもう一方のテーブルの行が存在しない場合、その行は結果に現れないことになる。
そのようなJOINをINNER JOINという。
これまでのJOINは全てINNER JOINだった。
これに対し、片方あるいは両方のテーブルについて、条件に合うもう一方のテーブルの行が存在しなくても、結果として出力するJOINをOUTER JOINという。

準備

  1. 次のSQLをファイルとして作成。(delete121.sql)

    
    
  2. ログイン画面から実行。

    oddtbs=> \i delete121.sql
    DELETE 1
    DELETE 4
    DELETE 24
    
    oddtbs=> select * from g01;
     gnum | color
    ------+-------
        2 | 赤
        3 | 緑
    (2 rows)
    
    oddtbs=> select * from c01;
     gnum | cnum | charge
    ------+------+--------
        1 |    1 | 田中
        1 |    2 | 佐藤
        1 |    3 | 鈴木
        1 |    4 | 松井
        3 |    1 | 村山
        3 |    2 | 森田
        3 |    3 | 原田
        3 |    4 | 島田
    (8 rows)
    
    oddtbs=> select * from s01;
     gnum | cnum | snum |    name
    ------+------+------+------------
        1 |    1 |    1 | 大石友美
        1 |    1 |    2 | 中村優作
        1 |    1 |    3 | 北川陽子
        1 |    1 |    4 | 望月慎太郎
        1 |    1 |    5 | 木村葉子
        1 |    1 |    6 | 藤田智子
    ...
    ...
        2 |    4 |    4 | 鈴木崇宏
        2 |    4 |    5 | 田原直彦
        2 |    4 |    6 | 岡本暁
    (48 rows)
    

INNER JOIN

INNER JOINの結果をNATURAL JOINを例に確認する。

oddtbs=> select * from g01 natural join c01 order by gnum, cnum;
 gnum | color | cnum | charge
------+-------+------+--------
    3 | 緑    |    1 | 村山
    3 | 緑    |    2 | 森田
    3 | 緑    |    3 | 原田
    3 | 緑    |    4 | 島田
(4 rows)

oddtbs=> select * from g01 natural join s01 order by gnum, cnum, snum;
 gnum | color | cnum | snum |    name
------+-------+------+------+------------
    2 | 赤    |    1 |    1 | 大塚千恵子
    2 | 赤    |    1 |    2 | 中條佳澄
    2 | 赤    |    1 |    3 | 片瀬優富子
    2 | 赤    |    1 |    4 | 平野今日子
    2 | 赤    |    1 |    5 | 木村ヒトミ
    2 | 赤    |    1 |    6 | 長田めぐみ
    2 | 赤    |    2 |    1 | 石田美奈
...
...
    2 | 赤    |    4 |    4 | 鈴木崇宏
    2 | 赤    |    4 |    5 | 田原直彦
    2 | 赤    |    4 |    6 | 岡本暁
(24 rows)

oddtbs=> select * from c01 natural join s01 order by gnum, cnum, snum;
 gnum | cnum | charge | snum |    name
------+------+--------+------+------------
    1 |    1 | 田中   |    1 | 大石友美
    1 |    1 | 田中   |    2 | 中村優作
    1 |    1 | 田中   |    3 | 北川陽子
    1 |    1 | 田中   |    4 | 望月慎太郎
    1 |    1 | 田中   |    5 | 木村葉子
    1 |    1 | 田中   |    6 | 藤田智子
    1 |    2 | 佐藤   |    1 | 石川大地
...
...
    1 |    4 | 松井   |    4 | 鈴木佐知子
    1 |    4 | 松井   |    5 | 田村洋子
    1 |    4 | 松井   |    6 | 岡村ひろ子
(24 rows)

いずれも、両テーブルに共通する組み合わせしか表示されていない。

LEFT (OUTER) JOIN

LEFT (OUTER) JOINは、演算子の左側のテーブルに関しては、組み合わせとして条件に合う行が右側のテーブルに存在していなくても表示する。
その場合、右側のテーブルの列(を使用するデータ)は当然NULLとなる。

oddtbs=> select * from g01 natural left join c01 order by gnum, cnum;
 gnum | color | cnum | charge
------+-------+------+--------
    2 | 赤    |      |
    3 | 緑    |    1 | 村山
    3 | 緑    |    2 | 森田
    3 | 緑    |    3 | 原田
    3 | 緑    |    4 | 島田
(5 rows)

oddtbs=> select * from g01 natural left join s01 order by gnum, cnum, snum;
 gnum | color | cnum | snum |    name
------+-------+------+------+------------
    2 | 赤    |    1 |    1 | 大塚千恵子
    2 | 赤    |    1 |    2 | 中條佳澄
    2 | 赤    |    1 |    3 | 片瀬優富子
    2 | 赤    |    1 |    4 | 平野今日子
    2 | 赤    |    1 |    5 | 木村ヒトミ
    2 | 赤    |    1 |    6 | 長田めぐみ
    2 | 赤    |    2 |    1 | 石田美奈
...
...
    2 | 赤    |    4 |    5 | 田原直彦
    2 | 赤    |    4 |    6 | 岡本暁
    3 | 緑    |      |      |
(25 rows)

oddtbs=> select * from c01 natural left join s01 order by gnum, cnum, snum;
 gnum | cnum | charge | snum |    name
------+------+--------+------+------------
    1 |    1 | 田中   |    1 | 大石友美
    1 |    1 | 田中   |    2 | 中村優作
    1 |    1 | 田中   |    3 | 北川陽子
    1 |    1 | 田中   |    4 | 望月慎太郎
    1 |    1 | 田中   |    5 | 木村葉子
    1 |    1 | 田中   |    6 | 藤田智子
    1 |    2 | 佐藤   |    1 | 石川大地
...
...
    1 |    4 | 松井   |    5 | 田村洋子
    1 |    4 | 松井   |    6 | 岡村ひろ子
    3 |    1 | 村山   |      |
    3 |    2 | 森田   |      |
    3 |    3 | 原田   |      |
    3 |    4 | 島田   |      |
(28 rows)

左側のテーブルについては右側のテーブルに条件に合う行がなくても表示されている。
その際、右のテーブルの列はNULLとなっている。

RIGHT (OUTER) JOIN

RIGHT (OUTER) JOINはLEFT (OUTER) JOINの逆で、右側のテーブルに関して全ての行を表示する。

oddtbs=> select * from g01 natural right join c01 order by gnum, cnum;
 gnum | color | cnum | charge
------+-------+------+--------
    1 |       |    1 | 田中
    1 |       |    2 | 佐藤
    1 |       |    3 | 鈴木
    1 |       |    4 | 松井
    3 | 緑    |    1 | 村山
    3 | 緑    |    2 | 森田
    3 | 緑    |    3 | 原田
    3 | 緑    |    4 | 島田
(8 rows)

oddtbs=> select * from g01 natural right join s01 order by gnum, cnum, snum;
 gnum | color | cnum | snum |    name
------+-------+------+------+------------
    1 |       |    1 |    1 | 大石友美
    1 |       |    1 |    2 | 中村優作
    1 |       |    1 |    3 | 北川陽子
    1 |       |    1 |    4 | 望月慎太郎
    1 |       |    1 |    5 | 木村葉子
    1 |       |    1 |    6 | 藤田智子
    1 |       |    2 |    1 | 石川大地
...
...
    2 | 赤    |    4 |    4 | 鈴木崇宏
    2 | 赤    |    4 |    5 | 田原直彦
    2 | 赤    |    4 |    6 | 岡本暁
(48 rows)

oddtbs=> select * from c01 natural right join s01 order by gnum, cnum, snum;
 gnum | cnum | charge | snum |    name
------+------+--------+------+------------
    1 |    1 | 田中   |    1 | 大石友美
    1 |    1 | 田中   |    2 | 中村優作
    1 |    1 | 田中   |    3 | 北川陽子
    1 |    1 | 田中   |    4 | 望月慎太郎
    1 |    1 | 田中   |    5 | 木村葉子
    1 |    1 | 田中   |    6 | 藤田智子
...
...
    2 |    4 |        |    4 | 鈴木崇宏
    2 |    4 |        |    5 | 田原直彦
    2 |    4 |        |    6 | 岡本暁
(48 rows)

FULL (OUTER) JOIN

FULL (OUTER) JOINは演算子の両側のテーブル関して全ての行を表示する。

oddtbs=> select * from g01 natural full join c01 order by gnum, cnum;
 gnum | color | cnum | charge
------+-------+------+--------
    1 |       |    1 | 田中
    1 |       |    2 | 佐藤
    1 |       |    3 | 鈴木
    1 |       |    4 | 松井
    2 | 赤    |      |
    3 | 緑    |    1 | 村山
    3 | 緑    |    2 | 森田
    3 | 緑    |    3 | 原田
    3 | 緑    |    4 | 島田
(9 rows)

oddtbs=> select * from g01 natural full join s01 order by gnum, cnum, snum;
 gnum | color | cnum | snum |    name
------+-------+------+------+------------
    1 |       |    1 |    1 | 大石友美
    1 |       |    1 |    2 | 中村優作
    1 |       |    1 |    3 | 北川陽子
    1 |       |    1 |    4 | 望月慎太郎
    1 |       |    1 |    5 | 木村葉子
    1 |       |    1 |    6 | 藤田智子
    1 |       |    2 |    1 | 石川大地
...
...
    2 | 赤    |    4 |    5 | 田原直彦
    2 | 赤    |    4 |    6 | 岡本暁
    3 | 緑    |      |      |
(49 rows)

oddtbs=> select * from c01 natural full join s01 order by gnum, cnum, snum;
 gnum | cnum | charge | snum |    name
------+------+--------+------+------------
    1 |    1 | 田中   |    1 | 大石友美
    1 |    1 | 田中   |    2 | 中村優作
    1 |    1 | 田中   |    3 | 北川陽子
    1 |    1 | 田中   |    4 | 望月慎太郎
    1 |    1 | 田中   |    5 | 木村葉子
    1 |    1 | 田中   |    6 | 藤田智子
    1 |    2 | 佐藤   |    1 | 石川大地
...
...
    2 |    1 |        |    1 | 大塚千恵子
    2 |    1 |        |    2 | 中條佳澄
    2 |    1 |        |    3 | 片瀬優富子
...
...
    3 |    2 | 森田   |      |
    3 |    3 | 原田   |      |
    3 |    4 | 島田   |      |
(52 rows)