Oracle inner/outer/nature join

Oracle inner/outer/nature join

prerequisite:

create table tab1(id smallint, name char(6), value varchar(10), primary key(id));

create table tab2(id smallint, name char(6), score int, primary key(id));

insert into tab1 values(1, 'AAAAAA', 'aaaaaaaaaa');

insert into tab1 values(2, 'BBBBBB', 'bbbbbbbbbb');

insert into tab1 values(3, 'CCCCCC', 'cccccccccc');

insert into tab2 values(1, 'AAAAAA', 100);

insert into tab2 values(2, 'DDDDDD', 101);

insert into tab2 values(4, 'EEEEEE', 102);

Inner Join

select tab1.id    id1,

       tab1.name  name1,

       tab1.value value1,

       tab2.id    id2,

       tab2.name  name2,

       tab2.score score2

  from tab1

  inner join tab2

    on tab1.id = tab2.id;

       ID1 NAME1  VALUE1            ID2 NAME2      SCORE2

---------- ------ ---------- ---------- ------ ----------

         1 AAAAAA aaaaaaaaaa          1 AAAAAA        100

         2 BBBBBB bbbbbbbbbb          2 DDDDDD        101

Outer Join

Left Outer Join

select tab1.id    id1,

       tab1.name  name1,

       tab1.value value1,

       tab2.id    id2,

       tab2.name  name2,

       tab2.score score2

  from tab1

  left join tab2

    on tab1.id = tab2.id;

       ID1 NAME1  VALUE1            ID2 NAME2      SCORE2

---------- ------ ---------- ---------- ------ ----------

         1 AAAAAA aaaaaaaaaa          1 AAAAAA        100

         2 BBBBBB bbbbbbbbbb          2 DDDDDD        101

         3 CCCCCC cccccccccc

Right Outer Join

select tab1.id    id1,

       tab1.name  name1,

       tab1.value value1,

       tab2.id    id2,

       tab2.name  name2,

       tab2.score score2

  from tab1

  right join tab2

    on tab1.id = tab2.id;

       ID1 NAME1  VALUE1            ID2 NAME2      SCORE2

---------- ------ ---------- ---------- ------ ----------

         1 AAAAAA aaaaaaaaaa          1 AAAAAA        100

         2 BBBBBB bbbbbbbbbb          2 DDDDDD        101

                                      4 EEEEEE        102

Full Outer Join

select tab1.id    id1,

       tab1.name  name1,

       tab1.value value1,      

       tab2.id    id2,

       tab2.name  name2,

       tab2.score score2

  from tab1

  full join tab2

    on tab1.id = tab2.id

    order by tab1.id;

       ID1 NAME1  VALUE1            ID2 NAME2      SCORE2

---------- ------ ---------- ---------- ------ ----------

         1 AAAAAA aaaaaaaaaa          1 AAAAAA        100

         2 BBBBBB bbbbbbbbbb          2 DDDDDD        101

         3 CCCCCC cccccccccc

                                      4 EEEEEE        102

Natural Join

select *

 from tab1

natural join tab2;

        ID NAME   VALUE           SCORE

---------- ------ ---------- ----------

         1 AAAAAA aaaaaaaaaa        100

Notice:

1. natural join automatically bind columns with same column name and column type.

2. same column name with different column type(e.g., char vs. int, but char vs. varchar can work smoothly) will cause natural join failure.

3. return columns contain all distinct column in both tables.

So,

select tab1.* from tab1 natural join tab2;

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier