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,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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |