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