5 Join [補足]

テキスト

  1. 丸 山テキスト  http://www.wakhok.ac.jp/DB/section2.4.4.html
  2. http://www.postgresql.jp/document/pg732doc/user/queries-table-expressions.html#QUERIES-FROM

サンプル

test=# CREATE TABLE table1 (n integer, t text);
CREATE
test=# CREATE TABLE table2 (n integer, t text);
CREATE
test=# INSERT INTO table1 VALUES(1, 'asada');
INSERT 41898 1
test=# INSERT INTO table1 VALUES(2, 'inoue');
INSERT 41899 1
test=# INSERT INTO table1 VALUES(3, 'ueda');
INSERT 41900 1
test=# INSERT INTO table2 VALUES(1, 'asama');
INSERT 41901 1
test=# INSERT INTO table2 VALUES(2, 'inose');
INSERT 41902 1
test=# INSERT INTO table2 VALUES(3, 'ueda');
INSERT 41903 1
test=# SELECT * FROM table1;
 n |   t
---+-------
 1 | asada
 2 | inoue
 3 | ueda
(3 rows)

test=# SELECT * FROM table2;
 n |   t
---+-------
 1 | asama
 2 | inose
 3 | ueda
(3 rows)

SQL89形式のジョイン

test=# SELECT * FROM table1,table2 WHERE table1.n = table2.n;
 n |   t   | n |   t
---+-------+---+-------
 1 | asada | 1 | asama
 2 | inoue | 2 | inose
 3 | ueda  | 3 | ueda
(3 rows)

テーブル1 CROSS JOIN テーブル2

test=# SELECT * FROM table1 CROSS JOIN table2;
 n |   t   | n |   t
---+-------+---+-------
 1 | asada | 1 | asama
 1 | asada | 2 | inose
 1 | asada | 3 | ueda
 2 | inoue | 1 | asama
 2 | inoue | 2 | inose
 2 | inoue | 3 | ueda
 3 | ueda  | 1 | asama
 3 | ueda  | 2 | inose
 3 | ueda  | 3 | ueda
(9 rows)

テーブル1 NATURAL JOIN テーブル2

test=# SELECT * FROM table1 NATURAL JOIN table2;
 n |  t
---+------
 3 | ueda
(1 row)

テーブル1  JOIN テーブル2 ON 条件式

テーブル1  JOIN テーブル2 USING(列リスト)

OUTER JOIN(外部結合) 

test=# SELECT * FROM table1 LEFT JOIN table2 USING(t);
   t   | n | n
-------+---+---
 asada | 1 |
 inoue | 2 |
 ueda  | 3 | 3
(3 rows)
test=# SELECT * FROM table1 RIGHT JOIN table2 USING(t);
   t   | n | n
-------+---+---
 asama |   | 1
 inose |   | 2
 ueda  | 3 | 3
(3 rows)
test=# SELECT * FROM table1 FULL  JOIN table2 USING(t);
   t   | n | n
-------+---+---
 asada | 1 |
 asama |   | 1
 inose |   | 2
 inoue | 2 |
 ueda  | 3 | 3
(5 rows)