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

ITPub博客

left join,right join,full join的一点总结

最新文章