看到有些人经常问，把自己的一些体会简单举个例子，详细的东西还是需要自己慢慢体会的，比如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
|
|
|
