內連線、外連線總結

shuangoracle發表於2011-07-15
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
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24496749/viewspace-1052514/，如需轉載，請註明出處，否則將追究法律責任。

相關文章