not in、not exists 及用join改写
来源:互联网 发布:广东省 人工智能 政策 编辑:程序博客网 时间:2024/05/22 02:05
/*我们接着用上次建的两个表。数据如下*/
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 nullorder 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>
/*当然,以上语句就不能用(+)改写了*/
- not in、not exists 及用join改写
- sql----改写 in ,exists,not in ,not exists
- sql not in、left join、not exists
- not in / not exists /外连接 互相改写
- mysql的left jion改写not in或not exists
- NOT IN改为LEFT JOIN或NOT EXISTS的实现
- NOT IN改为LEFT JOIN或NOT EXISTS的实现
- not in,not exists,left join性能对比
- NOT IN、JOIN、IS NULL、NOT EXISTS效率对比
- NOT IN NOT EXISTS LEFT JOIN/ IS NULL
- NOT IN、JOIN、IS NULL、NOT EXISTS效率对比
- SQL语句优化—in,not in,exists,not exists, left join...on
- IN, EXISTS, NOT IN, NOT EXISTS
- in、not in、exists、not exists
- exists,in 和 not in ,not exists
- IN/NOT IN---EXISTS/NOT EXISTS理解
- in、exists、not in、not exists
- oracle in,exists,not in,not exists
- 系统日志处理系列 (一)如何使用logging、commons-logging、log4j输出日志
- 北斗系统的通信过程
- uboot分析
- 透彻理解android中Activity生命周期
- Md5 加密算法
- not in、not exists 及用join改写
- input onchange事件
- VC++ 获取ListControl选中行
- 批量删除某一目录下所有文件
- android.accessibilityservice包介绍
- MRR and Analyze tool
- Windows XP环境下Bundler的安装和配置
- java数字转换为字符串,长度不够前面补0
- MyEclipse必须会用的快捷键