--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

--从起初的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

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的例子(大家有兴趣的可以自己分析)-------------------------
--第1组--
SELECT * FROM a,b WHERE a.ID=b.ID;
SELECT * FROM a JOIN b ON a.ID=b.ID;

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);