转自：http://www.itpub.net/viewthread.php?tid=1410369&extra=page%3D2%26amp%3Bfilter%3Ddigest
left join,right join,full join的一点总结
看到有些人经常问，把自己的一些体会简单举个例子，详细的东西还是需要自己慢慢体会的，比如from a,b where a.id=b.id(+) and b.name='a'这个b.name没有+号则相当于普通的连接（
准确地说是先外连接后过滤），还有些乱七八糟的join,比如一个表在同一层作为几个表的从表，join条件带or或子查询的，要注意+号有限制，那么ansi join更加强大，没有乱七八糟的限制，能简单实现更复杂的join，而且ansi join的好处就是结构清晰，可读性强。
这些细节还是对照文档自己体会，我下面将要说的是一些容易迷惑的地方。
--scripts:
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a(ID NUMBER,NAME VARCHAR2(10));
CREATE TABLE b(ID NUMBER,NAME VARCHAR2(10));
INSERT INTO a VALUES(1,'a');
INSERT INTO a VALUES(2,'b');
INSERT INTO a VALUES(3,'c');
INSERT INTO b VALUES(1,'a');
INSERT INTO b VALUES(2,'b');
INSERT INTO b VALUES(4,'d');
COMMIT;
SQL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
SQL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
--用普通的oracle +语法改写ansi full join
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID;
ID ID
---------- ----------
1 1
2 2
3
4
--full join全外连接的含义（结果）是：
--1.选出全部满足连接条件的结果
--2.以左表为基准表(left join)得到的结果，当然不要包含1选择的结果
--3.以右表为基准表(right join)得到的结果，同样不包含1的结果
--4.或者第1步不要，2选出以左表为基准表(left join)得到的结果，3选出以右表为基准表(right join)得到的结果，不包含2的内连接结果
--如果连接条件1:1,可以用union
--因为union的前面1条SQL的含义包含了以左表a为基准表的结果
--union后的1条SQL的含义也包含了以右表b为基准表的结果，他们之间的交集在于内连接
--用完union之后，完全匹配条的结果会被剔重，因��是1:1的关系，剔重不影响full join的结果，当然这不是非常好的方法，虽然很简单
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
1 1
2 2
3
4
--非1:1应该用UNION ALL并且第2个语句在+号处的列只选出纯外连接结果
INSERT INTO a VALUES(1,'a');
COMMIT;
SQL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
1 a
SQL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
-现在如果还用上面的UNION来改写，因为b.id=1会对应2条a.id=1,那么full join应该选出2条id=1的记录，用union剔重就不正确了
--从起初的full join含义出发，union前面的sql选出了以a为基准表的结果（内连接+纯以a的外连接）
--下面一条sql是以b表为基准表的结果
--OK，很简单，只要将union上面或下面一条sql选出是完全左/右连接(不包含完全匹配条件的结果)的结果即可。
--当然，这个简单的SQL不用考虑NULL的问题，就算ID允许NULL，因为是外连接，id is null的行会被包含进去
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
--1:n用UNION不正确
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
1 1
2 2
3
4
--下面2条正确
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.ID IS NULL;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
3
1 1
1 1
2 2
4
FULL JOIN继续上面的扩展
SQL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
1 a
SQL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
--带单列条件的full join
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID AND a.NAME='a';
ID ID
---------- ----------
1 1
2
3
1 1
4
2
6 rows selected
--结果很奇怪吗？明明id=2的都是匹配的，怎么两端都不显示另外一个呢，请看下面
--这个含义是什么呢？
--a与b连接，完全匹配的条件是a.id=b.id and a.name='a' id=1的
--a left join b 连接条件也是a.id=b.id and a.name='a'，a是基准表，不满足a.id=b.id and a.name='a'的连接行b端置NULL，前面完全匹配不考虑的时候
--当a.id=2与b.id=2是匹配的，但是匹配上的a.name='b'不满足a.name='a'的条件，因此b端是没有找到匹配行的
--a right join b，b是基准表，其它同上
--用+改写之
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+) AND a.NAME='a'
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a' AND a.ID IS NULL;
ID ID
---------- ----------
1 1
1 1
2
4
--发现到什么没有？？改写的是不正确的，要注意where的含义，where可是filter啊
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+) AND a.NAME='a';
ID ID
---------- ----------
1 1
1 1
<==equal==>
SQL> SELECT a.ID,b.ID
2 FROM (SELECT ID,NAME FROM a WHERE a.NAME='a') a
3 ,b WHERE a.ID=b.ID(+);
ID ID
---------- ----------
1 1
1 1
--改写错误，这个的含义是选出a.name='a'的然后作为基表与b外连接，这样结果集明显可能会变少
--一步步来，先用ansi left join改写
SQL> SELECT a.ID,b.ID
2 FROM a LEFT JOIN b
3 ON a.ID=b.ID AND a.NAME='a';
ID ID
---------- ----------
1 1
2
3
1 1
--这就对了，那么现在看用+号怎么改写呢？这个语句的含义是在a是基表，在a.name='a'的情况下以a表为基准表与b外连接，
--OK，decode搞定
SQL> SELECT a.ID,b.ID
2 FROM a,b
3 WHERE a.id=decode(a.NAME,'a',b.ID(+));
ID ID
---------- ----------
1 1
2
3
1 1
--正确了，又有问题了，为什么上面的不正确，下面的
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a';
ID ID
---------- ----------
1 1
1 1
2
--正确呢，因为下面的相当于
SQL> SELECT a.ID,b.ID
2 FROM a RIGHT JOIN b
3 ON a.ID=b.ID AND a.NAME='a';
ID ID
---------- ----------
1 1
1 1
4
2
--因为这个基表是b了，而a是从表，外连接含义是不满足a.ID=b.ID AND a.NAME='a'的a端��空，但是b全部要选到
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a' ;
ID ID
---------- ----------
1 1
1 1
2
4
--因此正确。
--结论：如果类似于from a left join b on a.col=b.col and a.coln=....
--单个列选择条件的列是基表的用decode和+改写，不能用一般的改写，若不是基表的可以简单改写
--最终用+号改写为
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.id=decode(a.NAME,'a',b.ID(+))
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a' AND a.ID IS NULL;
ID ID
---------- ----------
1 1
2
3
1 1
2
4
6 rows selected
自己刚写的一些用于理解JOIN的简单例子，按组放在一起，大家可以对比是否等价，等价的原因是什么？不等价的原因又是什么？是否报错？最主要的是弄清楚语句的含义，当然每个语句的变种可能有N种，但是只要理解了JOIN的含义，掌握关系型SQL最基本的原理，一切将会迎刃而解。
SQL也即找准关系第1、以正确的关系找到正确的匹配语法第2、最后就是troubleshooting与tuning...
---------------------------------一些用于理解join的例子(大家有兴趣的可以自己分析)-------------------------
--第1组--
SELECT * FROM a,b WHERE a.ID=b.ID;
SELECT * FROM a JOIN b ON a.ID=b.ID;
--第2组--
SELECT * FROM a LEFT JOIN b ON a.ID=b.ID AND a.NAME='a';
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND a.NAME='a';
SELECT * FROM a,b WHERE a.ID=decode(a.NAME,'a',b.ID(+));
SELECT * FROM
(SELECT * FROM a WHERE a.NAME='a') a LEFT JOIN b ON a.ID=b.ID;
--第3组--
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID AND a.NAME='a';
SELECT * FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a';
SELECT * FROM a,b WHERE a.ID(+)=b.ID AND a.NAME='a';
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID WHERE a.NAME='a';
SELECT * FROM a,b WHERE b.id=decode(a.NAME(+),'a',a.ID(+));
SELECT * FROM
(SELECT * FROM a WHERE a.NAME='a') a RIGHT JOIN b ON a.ID=b.ID;
--第4组--
SELECT * FROM a LEFT JOIN b ON a.ID=b.ID AND b.ID IS NULL;
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND b.ID(+) IS NULL;
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL;
--第5组--
DROP TABLE c;
CREATE TABLE c
AS
SELECT 1 ID,'a' NAME FROM dual UNION ALL
SELECT 5 ID,'x' NAME FROM dual;
SELECT * FROM a,b,c WHERE a.ID(+)=b.ID AND a.ID(+)=c.ID;
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID
RIGHT JOIN c ON a.ID=c.ID;
SELECT * FROM a,b WHERE a.ID(+)=b.ID OR a.NAME=b.NAME;
SELECT * FROM a,b WHERE a.ID(+)=b.ID OR a.NAME(+)=b.NAME;
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID OR a.NAME=b.NAME;
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND a.NAME IN (SELECT 'a' FROM dual);
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND b.NAME(+) IN (SELECT 'a' FROM dual);
SELECT * FROM a LEFT JOIN b ON a.ID=b.ID AND b.NAME IN (SELECT 'a' FROM dual);
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-2 9:00:00 |
赞(
操作
