# 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)``````