有三个表A, B, AB
A:
AID
1
2
3
B:
BID
1
2
3
AB
AID(FK) BID(FK)
1 1
2 2
3 3执行以下SQL:
select * from a left outer join b on aid in (select aid from ab where b.bid = ab.bid);
结果为:
AID BID
1 1
2 2
3 3
但是执行
select * from a left outer join b on bid in (select ab.bid from ab where a.aid = ab.aid);
结果为:
AID BID
1 1
2 1
3 1
为什么不一样呢?
A:
AID
1
2
3
B:
BID
1
2
3
AB
AID(FK) BID(FK)
1 1
2 2
3 3执行以下SQL:
select * from a left outer join b on aid in (select aid from ab where b.bid = ab.bid);
结果为:
AID BID
1 1
2 2
3 3
但是执行
select * from a left outer join b on bid in (select ab.bid from ab where a.aid = ab.aid);
结果为:
AID BID
1 1
2 1
3 1
为什么不一样呢?
left outer join b c on b.bid=b.bid
AID BID
1 1
2 1
3 1
这个例子举得不恰当,在这个例子中不会出现这种情况
看看两个查询得出的记录数是否相同,如果相同的话就是排序问题了
各加上order by a.aid,b.bid
看看是否相同
所以 只需要
select * from ab where aid = bid
---------- ----------
1 1
2 2
3 3SQL> select * from a left outer join b on bid in (select ab.bid from ab where a.aid = ab.aid); AID BID
---------- ----------
1 1
2 1
3 1SQL> select * from b left outer join a on bid in (select ab.bid from ab where a.aid = ab.aid); BID AID
---------- ----------
3 3
2 2
1 1
---------- ----------
3 1
3 2
3 3
2 1
2 2
2 3
1 1
1 2
1 3已选择9行。
select * from a left outer join b on bid in (select ab.bid from ab where a.aid = ab.aid);
在第一条语句的子查询中使用的是b.bid作为比较条件
而在第二条语句中的子查询里使用的是a.aid作为比较的条件
而外层的select语句中都是a和b两张表 而且位置相同
这就是区别
= ab.bid); AID BID
---------- ----------
1 1
2 2
3 3SQL> select * from a left outer join b on bid in (select ab.bid from ab where a.
aid = ab.aid); AID BID
---------- ----------
1 1
2 1
3 1SQL> select * from b left outer join a on bid in (select ab.bid from ab where a.
aid = ab.aid); BID AID
---------- ----------
1 1
2 2
3 3
select * from a left outer join b on bid in (select ab.bid from ab where a.
aid = ab.aid);
这里子查询的a.aid没有和前面的a表相应记录对应?王海的数据库数什么版本的
我的是11g没有这个问题。下次到公司试试9i版本下的结果
---
1
2
3SQL> select * from b;BID
---
1
2
3SQL> select * from ab;AID BID
--- ---
1 1
2 2
3 3
SQL> select * from a left outer join b on aid in (select aid from ab where b.bid = ab.bid);AID BID
--- ---
1 1
2 2
3 3SQL> select * from a left outer join b on bid in (select ab.bid from ab where a.aid = ab.aid);AID BID
--- ---
1 1
2 2
3 3
oracle 11g 运行结果