Conditions ON, USING, and NATURAL

You can use the join conditions ON, USING, and NATURAL to specify join criteria.

The ON clause is the most flexible. It can handle all join criteria, and, in certain cases, non-join criteria.
The USING and NATURAL clauses provide convenient ways to specify joins when the join columns have the same name.

Cross join

You cannot use an ON, USING, or NATURAL condition with a cross join.

Inner join

The following examples show inner joins.

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)

The following statement is equivalent:

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

USING 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)

NATURAL

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

Left outer join

The following examples show left outer joins.

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)

USING 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)

NATURAL

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

Right outer join

The following examples show right outer joins.

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)

USING 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)

NATURAL

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

Full outer join

The following examples show full outer joins.

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)

USING 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)

NATURAL

   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)