Oracle inner/outer/nature join
来源:互联网 发布:c4d r17 mac 序列号 编辑:程序博客网 时间:2024/06/06 20:37
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
- Oracle inner/outer/nature join
- ORACLE- join,inner join 与 left join, left outer join
- inner join 和outer join
- Mysql Inner Join , Outer Join
- Linq INNER JOIN / OUTER JOIN
- inner join 和 outer join
- Learn SQL Join,Inner Join, Outer Join
- inner/outer join in hibernate
- inner/outer join in hibernate
- inner/outer join in hibernate
- oracle 内连接(inner join)、外连接(outer join)、全连接(full join)
- oracle 内连接(inner join)、外连接(outer join)、全连接(full join)
- oracle 内连接(inner join)、外连接(outer join)、全连接(full join)
- inner join&left outer join&right outer join
- inner join&left outer join&right outer join 区别
- left outer join,inner join,right outer join的区别
- inner join&left outer join&right outer join
- inner join, left outer join, right outer join
- leetcode之判断是否是同一棵树
- 开篇之笔
- C语言程序设计实践(OJ)-用循环解决问题
- html文档基本介绍
- 保垒机SSH登录脚本
- Oracle inner/outer/nature join
- hdu1098Ignatius's puzzle泰勒展开/二项式展开
- Struts2学习笔记——001配置Strut2
- 字符操作(1)
- 解决dropdownlist选项重复问题
- Java类与类之间的关系
- xml解析,httputils网络请求,imageloder图片加载
- Logger
- ORACLE SQLNOTFOUND 100 vs. 1403