sql not in 的坑及改写为join
/*我们接着用上次建的两个表。数据如下*/
SQL> select * from l;
STR V
------ -
left_1 1
left_2 2
left_3 3
left_4 4
SQL> select * from r;
STR V
------- -
right_3 3
right_4 4
right_5 5
right_6 6
/*如果要返回l表中有而r表为没有的数据（v=1、2），那么用not in写应该如下*/
SQL> select * from l where v not in(select r.v from r);
STR V
------ -
left_1 1
left_2 2
/*如果r表中v有空值呢*/
SQL> insert into r values(null,null);
1 row inserted
/*结果集返回错误*/
SQL> select * from l where v not in(select r.v from r);
STR V
------ -
/*这时要增加条件 r.v is not null*/
SQL> select * from l where v not in(select r.v from r where r.v is not null);
STR V
------ -
left_1 1
left_2 2
/*not exists语句不受null影响，因为not exists中是等值关系*/
SQL> select * from l where not exists(selectnull from r where r.v = l.v);
STR V
------ -
left_1 1
left_2 2
/*以上两种都叫反联接，也可以用join改写解析如下*/
SQL> select l.*,r.* from l left join ron l.v = r.v order by 2;
STR V STR V
------ - ------- -
left_1 1
left_2 2
left_3 3 right_3 3
left_4 4 right_4 4
/*如上所示,l中有，而r中没有的，就是r.v为空的行，那么加上这个条件后，返回的就是所需数据了*/
SQL> select l.* from l left join r onl.v = r.v where r.v is null order by 2;
STR V
------ -
left_1 1
left_2 2
/*如果能前的(+)写法，应该如下，为了便于理解，我们仍分步执行来看*/
SQL> select left_str,left_v from (selectl.str as left_str,l.v as left_v,r.str as right_str,r.v as right_v from l,rwhere l.v = r.v(+) order by 2);
LEFT_STR LEFT_V
-------- ------
left_1 1
left_2 2
left_3 3
left_4 4
SQL> select left_str,left_v from (selectl.str as left_str,l.v as left_v,r.str as right_str,r.v as right_v from l,rwhere l.v = r.v(+)) where right_v is null orderby 2;
LEFT_STR LEFT_V
-------- ------
left_1 1
left_2 2
/*简化后，语句如下*/
SQL> select l.* from l,r where l.v =r.v(+) and r.v is null order by 2;
STR V
------ -
left_1 1
left_2 2
/*有人把这两种弄混了，写为*/
select l.* from l left join r on (l.v =r.v) and r.v is null order by 2;
/*那么我们把两个表的数据都返回，看结果*/
SQL> select l.*,r.* from l left join ron (l.v = r.v) and r.v is null order by 2;
STR V STR V
------ - ------- -
left_1 1
left_2 2
left_3 3
left_4 4
/*以上这句整理一下就是*/
SQL> select l.*,r.* from l left join(select * from r where r.v is null)r on (l.v =r.v) order by 2;
STR V STR V
------ - ------- -
left_1 1
left_2 2
left_3 3
left_4 4
/*相当于是l与一空行join了*/
SQL> select * from r where r.v is null;
STR V
------- -
SQL>
/*对join的方法引申一部，改为full join*/
SQL> select l.*,r.* from l full join ron l.v = r.v order by 2,4;
STR V STR V
------ - ------- -
left_1 1
left_2 2
left_3 3 right_3 3
left_4 4 right_4 4
right_5 5
right_6 6
7 rows selected
SQL>
/*看上述结果，对别对应l.v为空，与r.v为空就是两个表不匹配的行*/
SQL> select l.*,r.* from l full join ron l.v = r.v where (l.v is null or r.v is null)order by 2,4;
STR V STR V
------ - ------- -
left_1 1
left_2 2
right_5 5
right_6 6
SQL>
/*当然，以上语句就不能用(+)改写了*/
---------------------
原文链接：https://blog.csdn.net/jgmydsai/article/details/15810295
