About

A natural join is an equi-join on the same attribute name (column) of each relation (table, query, …)

It's a shorthand equi-join where the equality predicate applied to all shared attributes.

Articles Related

Example

• Create the table

create table t1 ( x int primary key, y int, z int );
create table t2 ( x int primary key, y references t1, a int );

• And insert

insert into t1 values ( 1, 1 , 100);
insert into t1 values ( 2, 2 , 200);
insert into t2 values ( 1,  1 , 300);
insert into t2 values ( -2, 1 , 400);
insert into t2 values ( 2,  2 , 500);
insert into t2 values ( -4, 2 , 600);

• Natural Join Select

select * from t1 natural join t2;
-- ie select * from t1 natural join t2 where t1.x = t2.x and t1.y = t2.y

X          Y          Z          A
---------- ---------- ---------- ----------
         1          1        100        300
         2          2        200        500

• Not the same than a primary/foreign key join (ie t1.x = t2.y )

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