Natural Join

About

An equi-join also known as simple join link tables based on the equality between the values in the column of one table and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two tables.

The restriction conforms to the following syntax:

Table1.column_a = Table2.column_a

In a normalized database the columns used in an equi-join are usually the primary key from one table and the foreign key in the other.

The result of an equi-join can be defined as the outcome of :

first taking the Cartesian product (or SQL - Cross Join / Cartesian Product) of all records in the tables (combining every record in table A with every record in table B)
then return all records which satisfy the join predicate.

Articles Related

Example

Example 1

Example 2

table1 (inner set)	table2 (outer set)
Column ID	Column ID	Column ID_2
A	A
B	B	A
D	C	C
	A	A

gerardnico@orcl>select table1.id "ID_table1", table2.id "ID_table2", table2.id_2 "ID2_table2"
  2  FROM table1, table2
  3  WHERE table1.id = table2.id;
 
ID_table1  ID_table2  ID2_table2
---------- ---------- ----------
A          A          A
A          A          
B          B          A

The value C from the table1 and D from the table2 disappear because they don't exist in the two tables.

Natural Join

A natural join clause permit you to avoid to specify the join condition. The resultant join is an inner join performed on the primary/foreign key relationship.

Example

ops$tkyte%ORA10GR2> CREATE TABLE t1 ( x int PRIMARY KEY, y int, z int );
 
TABLE created.
 
ops$tkyte%ORA10GR2> CREATE TABLE t2 ( x int PRIMARY KEY, y REFERENCES t1, a int );
 
TABLE created.

Obviously, to join t1 to t2, you would use t1.x = t2.y, the primary/foreign key..

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO t1 VALUES ( 1, 1 , 100);
 
1 row created.
 
ops$tkyte%ORA10GR2> INSERT INTO t1 VALUES ( 2, 2 , 200);
 
1 row created.
 
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( 1,  1 , 300);
 
1 row created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( -2, 1 , 400);
 
1 row created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( 2,  2 , 500);
 
1 row created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( -4, 2 , 600);
 
1 row created.
 
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM t1 NATURAL JOIN t2;
 
         X          Y          Z          A
---------- ---------- ---------- ----------
         1          1        100        300
         2          2        200        500

Hmmm…..

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM t1 JOIN t2 ON (t1.x = t2.y);
 
         X          Y          Z          X          Y          A
---------- ---------- ---------- ---------- ---------- ----------
         1          1        100          1          1        300
         1          1        100         -2          1        400
         2          2        200          2          2        500
         2          2        200         -4          2        600

oh, there we go

Documentation / Reference

More ANSI SQL Natural Join...
Join