oracle中exists与in的使用方法
用not exists 代替not in
select * from tsp_product p where not exists(select '' from tsp_orderitem i where p.id=i.product_id)
select * from tsp_product p where id not in(select product_id from tsp_orderitem i where p.id=i.product_id)
用exists 代替in
select * from tsp_product p where p.id in(select product_id from tsp_orderitem )
select * from tsp_product p where exists(select 'x' from tsp_orderitem i where p.id =i.product_id )
下面来分析为什么用用not exists 代替not in
有两个简单例子,以说明 “exists”和“in”的效率问题
1) select * from t1 where exists(select 1 from t2 where t1.a=t2.a) ;
t1数据量小而t2数据量非常大时,t1<<t2 时,1) 的查询效率高。
2) select * from t1 where t1.a in (select t2.a from t2) ;
t1数据量非常大而t2数据量小时,t1>>t2 时,2) 的查询效率高。
union
把两张表的数据合起来,如有重复行,只取一行
union all
把两张表的数据合起来,不过滤重复行
minus
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
oracle有这样的一些约定
1.select语句必须返回相同的列数,如果列数不同的话,可以选择串代替列。
2.select语句中相应的列必须有相同的数据类型,长度可以不同
rollup
统计:select sum(s.totalamount),to_char(createdate,'yyyy-mm') from tsp_orders s group by rollup(to_char(createdate,'yyyy-mm')
connect by
select level,a. * from tsp_area a start with parent_id is null connect by prior id = parent_id
说明:创建类似树报表。
prior 强制报表的顺序变为从根到叶(如果prior是父辈)或从叶到根(如果prior是后代)
虽然where子句可以人树排除上体,但无法排除他们的子孙子(或者祖先,如果prior在等号的右边)
详细看看它他的区别
exists 用法:
请注意 1)句中的有颜色字体的部分 ,理解其含义;
其中 “select 1 from t2 where t1.a=t2.a” 相当于一个关联表查询,相当于
“select 1 from t1,t2 where t1.a=t2.a”
但是,如果你当当执行 1) 句括号里的语句,是会报语法错误的,这也是使用exists需要注意的地方。
“exists(xxx)”就表示括号里的语句能不能查出记录,它要查的记录是否存在。
因此“select 1”这里的 “1”其实是无关紧要的,www.3ppt.com换成“*”也没问题,它只在乎括号里的数据能不能查找出来,是否存在这样的记录,如果存在,这 1) 句的where 条件成立。
in 的用法:
继续引用上面的例子
“2) select * from t1 where t1.a in (select t2.a from t2) ”
这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应,一般来说,t1和t2这两个表的a字段表达的意义应该是一样的,否则这样查没什么意义。
打个比方:t1,t2表都有一个字段,表示工单号,但是t1表示工单号的字段名叫“ticketid”,t2则为“id”,但是其表达的意义是一样的,而且数据格式也是一样的。这时,用 2)的写法就可以这样:
“select * from t1 where t1.ticketid in (select t2.id from t2) ”
select name from employee where name not in (select name from student);
select name from employee where not exists (select name from student);
分享到:
相关推荐
oracle中exists_和in的效率问题详解
oracle数据库关于exists使用方法与in的比较
里面自己根据网上的资源整理出来的一份sql中in,exists,not in,not exists的使用方法以及注意事项等,有助于初学的朋友们借鉴。
“exists”和“in”是Oracle中,都是查询某集合的值是否存在在另一个集合,但对不同的数据有不同的用法,主要是在效率问题上存在很大的差别,以下有两个简单例子,以说明 “exists”和“in”的效率问题。
一直以来,大家认为exists比in速度快,其实是不准确的。且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么...
oracle in和exists性能解析
一个是问in exist的区别,一个是not in和not exists的区别
NULL 博文链接:https://wuaner.iteye.com/blog/1671927
Using a Subquery in the FROM Clause 4-10 Scalar Subquery Expressions 4-11 Scalar Subqueries: Examples 4-12 Correlated Subqueries 4-14 Using Correlated Subqueries 4-16 Using the EXISTS Operator 4-18 ...
oracle的sql优化方法 1.全表扫描和索引扫描 大数据量表尽量要避免全表扫描,全部扫描会按顺序每条记录扫描,对于>100万数据表影响很大。 Oracle中通过RowID访问数据是最快的方式 对字段进行函数转换,或者前...
with子句使用,集合操作,case应用,行列互换,4 exists与in、not exists与not in
尽量用not exists 或者外连接替代 not in 操作符。因为not in不能 应用表的索引 3。尽量不用<> 或者!= 操作符。不等于操作符是永远不会用到索引的,因此 对它的处理只会产生全表扫描,改为 a> XX or a 4.在设计...
§3.2.2 在参数值中使用特殊字符 65 §3.2.3 修改参数值 66 §3.2.4 显示当前参数值 69 §3.2.5 参数的使用 69 §3.2.6 参数的类型 69 §3.2.7 不能在参数文件中指定的参数 70 §3.2.8 当参数指定错误时怎么办? 70 ...
尽量使用“>=”,不要使用“>”,用EXISTS代替IN(外表数据小情况),用大于或小于代替不等于,用右模糊查询(LIKE ‘…%’)代替模糊查询,用UNION ALL代替UNION,union代替or,trancate代替delete等. 7. count(1)比...
1.8 SELECT子句中避免使用 ‘ *‘ 7 1.9 减少访问数据库的次数 7 1.10 使用DECODE函数来减少处理时间 8 1.11 删除重复记录 8 1.12 用TRUNCATE替代DELETE 9 1.13 尽量多使用COMMIT 9 1.14 计算记录条数 9 1.15 用...
很适合开发人员在编写SQL时注意,这里虽然说是Oracle的优化,其实,很多是标准SQL需要注意的写法。具有非常高的共通性。 1.选用适合的ORACLE优化器 2.HERE子句中的连接顺序 ...4.EXISTS与IN的使用场景 5.索引造成的大错
13.1.3 EXISTS及其相关子查询的使用 13.2 外部连接 13.2.1 Oracle9i以前版本中的外部连接的语法 13.2.2 现在的外部连接语法 13.2.3 用外部连接代替NOTIN 13.2.4 用NOTEXISTS代替NOTIN 13.3 自然连接和内部连接 13.4 ...
13.1.3 EXISTS及其相关子查询的使用 13.2 外部连接 13.2.1 Oracle9i以前版本中的外部连接的语法 13.2.2 现在的外部连接语法 13.2.3 用外部连接代替NOTIN 13.2.4 用NOTEXISTS代替NOTIN 13.3 自然连接和内部连接 13.4 ...