SQL - Natural Join

Data System Architecture

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.

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





Discover More
Relational Algebra Between Sql And Query Plan
Relational Operator - Equi-joins

An Equi-join is a join where the condition (predicate) is an equality. theta join An equi-join links two relations (tables, ...) on common values in a column in relation 1 with a column in relation...
Tableainnertableb
SQL - (Equi|Simple|Inner|Natural) Join

in SQL An equi-join also known as simple join link relation (tables, ...) based on the equality between the values in the column of one relation (table, ...) and the values in the column of another. Because...



Share this page:
Follow us:
Task Runner