oracle中join的用法
8i:
create table dali.test1(a int,b int);
create table dali.test2(a int,b int);
insert into dali.test1 values(1,456);
insert into dali.test1 values(2,427);
insert into dali.test2 values(1,45456);
insert into dali.test2 values(3,45656);
---内连接
select * from dali.test1 a, dali.test2 b where a.a=b.a;
---左连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+);
---右连接
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---完全连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+)
union
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---迪卡尔
select * from dali.test1, dali.test2;
'==================================================================
9i和sqlserver一样 left join,right join,full join
内连接结果:
A B A B
1 1 456 1 45456
'----------------------------
左连接结果:
A B A B
1 1 456 1 45456
2 2 427
'====================
右连接结果:
A B A B
1 1 456 1 45456
2 3 45656
'=============================
完全连接结果:
A B A B
1 1 456 1 45456
2 2 427
3 3 45656
'========================
笛卡尔结果:
A B A B
1 1 456 1 45456
2 2 427 1 45456
3 1 456 3 45656
4 2 427 3 45656
8i:
create table dali.test1(a int,b int);
create table dali.test2(a int,b int);
insert into dali.test1 values(1,456);
insert into dali.test1 values(2,427);
insert into dali.test2 values(1,45456);
insert into dali.test2 values(3,45656);
---内连接
select * from dali.test1 a, dali.test2 b where a.a=b.a;
---左连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+);
---右连接
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---完全连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+)
union
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---迪卡尔
select * from dali.test1, dali.test2;
'==================================================================
9i和sqlserver一样 left join,right join,full join
内连接结果:
A B A B
1 1 456 1 45456
'----------------------------
左连接结果:
A B A B
1 1 456 1 45456
2 2 427
'====================
右连接结果:
A B A B
1 1 456 1 45456
2 3 45656
'=============================
完全连接结果:
A B A B
1 1 456 1 45456
2 2 427
3 3 45656
'========================
笛卡尔结果:
A B A B
1 1 456 1 45456
2 2 427 1 45456
3 1 456 3 45656
4 2 427 3 45656