ON、USING、NATURAL の各条件

結合条件 ON、USING、NATURAL を使用して、結合基準を指定できます。

ON 節はもっとも柔軟な結合条件です。すべての結合基準を指定できる他、場合によっては非結合基準にも対応します。
USING 節と NATURAL 節は、join 列が同じ名前を持つ結合を指定するのに便利です。

クロス結合

ON、USING、NATURAL 条件をクロス結合で使用することはできません。

内部結合

内部結合の例を以下に示します。

ON join_condition

   SELECT * FROM cows_one INNER JOIN cows_two ON cows_one.cnumber =
cows_two.cnumber;
cnumber     | cbreed     | cnumber  | breeds
------------+------------+----------+---------
2           | Guernsey   | 2        | Jersey
3           | Angus      | 3        | Brown Swiss
(2 rows)

次の文はこれと同等です。

   SELECT * FROM cows_one, cows_two WHERE cows_one.cnumber =
cows_two.cnumber;

使用 join_column_list

   SELECT * FROM cows_one INNER JOIN cows_two USING (cnumber);
cnumber      | cbreed     | breeds
-------------+---------- -+--------------
2            | Guernsey   | Jersey
3            | Angus      | Brown Swiss
(2 rows)

自然

   SELECT * FROM cows_one NATURAL INNER JOIN cows_two;
cnumber       | cbreed     | breeds
--------------+------------+--------------
2             | Guernsey   | Jersey
3             | Angus      | Brown Swiss
(2 rows)

左外部結合

左外部結合の例を以下に示します。

ON join_condition

   SELECT * FROM cows_one LEFT JOIN cows_two ON cows_one.cnumber = 
cows_two.cnumber;
cnumber   |  cbreed    |cnumber    | breeds
----------+------------+-----------+-----------
1         | Holstein   |           |           
2         | Guernsey   | 2         | Jersey    
3         | Angus      | 3         | Brown Swiss
(3 rows)

使用 join_column_list

   SELECT * FROM cows_one LEFT JOIN cows_two USING (cnumber);
cnumber    |  cbreed     | breeds              
-----------+-------------+----------------------
1          | Holstein    |                       
2          | Guernsey    | Jersey                
3          | Angus       | Brown Swiss           
(3 rows)

自然

   SELECT * FROM cows_one NATURAL LEFT JOIN cows_two;
cnumber    |  cbreed     | breeds              
-----------+-------------+----------------------
1          | Holstein    |                      
2          | Guernsey    | Jersey               
3          | Angus       | Brown Swiss           
(3 rows)

右外部結合

右外部結合の例を以下に示します。

ON join_condition

   SELECT * FROM cows_one RIGHT JOIN cows_two ON cows_one.cnumber = 
cows_two.cnumber;
cnumber    |  cbreed    | cnumber   | breeds     
-----------+------------+-----------+-------------
2          | Guernsey   | 2         | Jersey       
3          | Angus      | 3         | Brown Swiss 
           |            | 4         | Ayrshire     
(3 rows)

使用 join_column_list

   SELECT * FROM cows_one RIGHT JOIN cows_two USING (cnumber);
cnumber    |  cbreed     | breeds      
-----------+-------------+---------------
2          | Guernsey    | Jersey        
3          | Angus       | Brown Swiss   
4          |             | Ayrshire      
(3 rows)

自然

   SELECT * FROM cows_one NATURAL RIGHT JOIN cows_two;
cnumber    | cbreed     | breeds      
-----------+------------+---------------
2          | Guernsey   | Jersey       
3          | Angus      | Brown Swiss   
4          |            | Ayrshire      
(3 rows)

全外部結合

全外部結合の例を以下に示します。

ON join_condition

   SELECT * FROM cows_one FULL OUTER JOIN cows_two ON cows_one.cnumber 
= cows_two.cnumber;
cnumber   |  cbreed    | cnumber   | breeds     
----------+------------+-----------+-------------
1         | Holstein   |           |              
2         | Guernsey   | 2         | Jersey       
3         | Angus      | 3         | Brown Swiss 
          |            | 4         | Ayrshire
(4 rows)

使用 join_column_list

   SELECT * FROM cows_one FULL OUTER JOIN cows_two USING (cnumber);
cnumber   | cbreed     | breeds            
----------+------------+------------------
1         | Holstein   |                  
2         | Guernsey   | Jersey            
3         | Angus      | Brown Swiss       
4         |            | Ayrshire          
(4 rows)

自然

   SELECT * FROM cows_one NATURAL FULL OUTER JOIN cows_two;
cnumber    |  cbreed    | breeds         
-----------+------------+------------------
1          | Holstein   |                 
2          | Guernsey   | Jersey          
3          | Angus      | Brown Swiss      
4          |            | Ayrshire         
(4 rows)