内连接、外连接总结
create table t1(id number,name varchar2(20));
create table t2(id number,name varchar2(20));
create table t3(id number,name varchar2(20));
insert into t1 values(1,'aa');
insert into t1 values(2,'bb');
insert into t1 values(3,'cc');
insert into t2 values(1,'aa');
insert into t2 values(4,'dd');
insert into t2 values(5,'ee');
insert into t3 values(6,'ff');
--左连接
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1 left join t2 on t1.id = t2.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
3 cc
2 bb
SQL>
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1,t2
3 where t1.id = t2.id(+);
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
3 cc
2 bb
--右连接
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1 right join t2 on t1.id = t2.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
5 ee
4 dd
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1,t2
3 where t1.id(+) = t2.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
5 ee
4 dd
--全连接
SQL> select t1.id, t1.name, t2.id, t2.name, t3.id, t3.name
2 from t1
3 full join t2
4 on t1.id = t2.id
5 full join t3
6 on t1.id = t3.id;
ID NAME ID NAME ID NAME
---------- -------------------- ---------- -------------------- ---------- --------------------
1 aa 1 aa
4 dd
5 ee
3 cc
2 bb
6 ff
6 rows selected
--内连接
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1,t2
3 where t1.id=t2.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
SQL> select t1.id,t1.name,t2.id,t2.name
2 from t1 join t2 on t1.id=t2.id;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 aa 1 aa
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24496749/viewspace-1052514/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
group_id和distinct
下一篇：
rpad和lpad
请登录后发表评论
登录
全部评论
