PL/SQL的问题

[windofsun]

问题见下面的帖子。解决以后两个帖子一起结。多谢各位大侠！

http://community.csdn.net/Expert/topic/3214/3214295.xml?temp=.9033319


如果用PL/SQL要用WHERE (+) 才正确，那么下面的T-SQL对应的PL/SQL应该如何写呢？

SELECT *
FROM
A LEFT JOIN B ON A.ID=B.A_ID
LEFT JOIN C ON A.ID=C.A_ID
LEFT JOIN D ON B.ID=D.B_ID
WHERE A.ID='001'

[txlicenhe]

是不是这样啊？没试过呃。
where a.id = b.a_id(+)
and a.id = c.a_id(+)
and b.id = d.b_id(+)
and a.id = '001'

[txlicenhe]

--测试：
create table A(id number, a1 number);
create table B(a_id number,id number,b1 number);
create table c(a_id number,c1 number);
create table d(b_id number,d1 number);

insert into a values(1,10);
insert into a values(2,10);
insert into a values(3,10);
insert into b values(1,1,21);
insert into b values(1,2,22);
insert into b values(2,3,23);
insert into c values(2,31);
insert into d values(2,42);


select * from a,b,c,d
where a.id = b.a_id(+)
and a.id = c.a_id(+)
and b.id = d.b_id(+)
and a.id = 1;


SELECT *
FROM
A LEFT JOIN B ON A.ID=B.A_ID
LEFT JOIN C ON A.ID=C.A_ID
LEFT JOIN D ON B.ID=D.B_ID
where a.id = 1;

/*

ID A1 A_ID ID B1 A_ID C1 B_ID D1
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 10 1 1 21
1 10 1 2 22 2 42

已选择2行。


ID A1 A_ID ID B1 A_ID C1 B_ID D1
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 10 1 2 22 2 42
1 10 1 1 21

已选择2行。
*/

[txlicenhe]

上面简单测试证明如下两个语句执行效果一样：

select * from a,b,c,d
where a.id = b.a_id(+)
and a.id = c.a_id(+)
and b.id = d.b_id(+)
and a.id = 1;


SELECT *
FROM
A LEFT JOIN B ON A.ID=B.A_ID
LEFT JOIN C ON A.ID=C.A_ID
LEFT JOIN D ON B.ID=D.B_ID
where a.id = 1;

[WilliamXiaoLiang]

大家说得很清楚了，我来做个总结：
tsql中的join on在9i中的实施分为两步
1、“联结”条件用on 、、、
2、记录集过滤用where 、、、
顶一下！
QQ:390958177
Email:[e-mail]

[dinya2003]

inner join on id=id : where id=id
left join on id=id : where id=id(+)
right join on id=id : where id(+)=id

[baojianjun]

大家說的對，我來湊熱鬧