看到有些人经常问,把自己的一些体会简单举个例子,详细的东西还是需要自己慢慢体会的,比如from a,b where a.id=b.id(+) and b.name='a'这个b.name没有+号则相当于普通的连接(
准确地说是先外连接后过滤),还有些乱七八糟的join,比如一个表在同一层作为几个表的从表,join条件带or或子查询的,要注意+号有限制,那么ansi join更加强大,没有乱七八糟的限制,能简单实现更复杂的join,而且ansi join的好处就是结构清晰,可读性强。
这些细节还是对照文档自己体会,我下面将要说的是一些容易迷惑的地方。
[table=80%,black][tr][td] --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
[/td][/tr][/table]
这些细节还是对照文档自己体会,我下面将要说的是一些容易迷惑的地方。
[table=80%,black][tr][td] --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
[/td][/tr][/table]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7655508/viewspace-759188/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7655508/viewspace-759188/