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,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,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,

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,

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,

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

原文链接：https://www.f2er.com/oracle/212203.html