`
zgqynx
  • 浏览: 1366031 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

N Vs Exist in SQL

阅读更多
N Vs Exist in SQL
原文如下:
http://oracle-online-help.blogspot.com/2006/11/in-vs-exist-in-sql.html

IN Vs EXISTS

The two are processed quite differently.


IN Clause

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The sub query is evaluated, distinct, indexed and then
joined to the original table -- typically.


As opposed to "EXIST" clause

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).


So, when is exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.


Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.


If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.
分享到:
评论

相关推荐

    金仓Kingbase日常运维SQL汇总.pdf

    - **SQL语句**:`SELECT relname, n.amname AS index_type FROM sys_class m, sys_am n WHERE m.relam = n.oid AND m.oid IN (SELECT b.indexrelid FROM sys_class a, sys_index b WHERE a.oid = b.indrelid AND a....

    sqlserver 各种判断是否存在(表名、函数、存储过程等)

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'database_name.schema_name.StoredProcedureName') AND type in (N'P', N'PC')) BEGIN PRINT '存在' END ELSE BEGIN PRINT '不存在' ...

    sql server 带列名导出至excel.doc

    , @sql = @sql + ',' + '[' + a.name + ']' + CASE WHEN b.name IN ('char', 'nchar', 'varchar', 'nvarchar') THEN 'text(' + CAST(CASE WHEN a.length > 255 THEN 255 ELSE a.length END AS varchar) + ')' ...

    sql导入导出成excel源代码

    WHERE b.name NOT IN ('image', 'text', 'uniqueidentifier', 'sql_variant', 'ntext', 'varbinary', 'binary', 'timestamp') AND object_id(@tbname) = id ``` 这段代码构建了一个 SQL 查询语句,用于获取指定表的...

    微软内部资料-SQL性能优化3

    SQL Server needs to lock data that does not exist! If no rows satisfy the WHERE condition the first time the range is scanned, no rows should be returned on any subsequent scans. Key range locks are ...

    Diamond.ADO.v2.00.for.Delphi.n.BCB.incl.sources-SSG

    Currently OLE DB providers exist for SQL Server, Oracle, Access, ODBC, Active Directory Services and the Index Server. Key features: High speed - fast, native access to data without BDE. ...

    利用 Oracle 和 PHP 管理分布式跟踪文件

    Using PHP and Oracle to manage your distributed trace files--------------------------------------------------------------------------------------Deployment InstructionsOracle Layer The Oracle JServer must be installed and exist in a valid state. Ensure that the directories specified in the parameters for USER_DUMP_DEST and BACKGROUND_DUMP_DEST are set up as utl_file_dir directives in the init.ora,...

    大学《数据库原理与应用》考试题库及答案.pdf

    - 相关子查询和 `exist` 引导的查询:一般情况下, **D.in引导的查询都可以用exist引导的查询实现**。`exist` 子查询通常比 `in` 子查询更高效,尤其是在子查询返回大量数据时。 9. **视图更新**: **A.行列子集...

    整理后java开发全套达内学习笔记(含练习)

    exist 存在, 发生 [ig'zist] '(SQL关键字 exists) extends (关键字) 继承、扩展 [ik'stend] false (关键字) final (关键字) finally (关键字) fragments 段落; 代码块 ['frægmәnt] FrameWork [java] 结构,...

    VisualFoxPro6.0中文版命令手册.pdf

    CREATE SQL VIEW 视图名 AS SELECT ... ``` Creates a view within the current database. If no database is open, it creates an independent view that cannot be stored. **Delete a View:** ```plaintext ...

    Mysql导出存储过程

    ErrorCode: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_...

    Delphi7.1 Update

    * TDataSetProvider generates incorrect SQL in response to a RefreshRecord request in some cases and fails to update all fields properly (Quality Central 4014). * When TDataSetProvider obtains the DB ...

    php developer interview

    ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE resources ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, ...

    Mybatis报错: org.apache.ibatis.exceptions.PersistenceException解决办法

    4. **数据源问题**:数据库连接或查询语句(`SQL: select * from person`)可能存在问题,导致无法正确执行查询。 ### 解决步骤 1. **检查构造函数**:确保`Person`类有一个公共的无参构造函数。如果之前为了某些...

    数据库原理与应用.docx

    - **IN与EXISTS**(B.in引导的查询都可以用exist引导的查询实现): IN和EXISTS都可以用来实现子查询,但是IN和EXISTS在性能和使用场景上有所不同。 #### 二十一、数据库恢复技术 - **恢复技术**(D.缓冲区技术): ...

    java常用词汇汇总

    #### exist 存在, 发生 [ig'zist] ('SQL关键字 exists') - **中文释义**:存在 - **用途**:在编程语言中,尤其是SQL中,`exists`关键字用于检查子查询是否返回行。 #### extends (关键字) 继承、扩展 [ik'stend] ...

    Hive用户指南 Hive user guide 中文版

    - **EXIST/IN子句**:正确使用这些子句可以优化查询性能。 - **Reducer个数**:合理设定Reducer数量可以提高查询速度。 - **合并MapReduce操作**:通过组合多个操作来减少MapReduce作业的次数。 - **Bucket与...

    框架集合错误解决方案

    java.lang.IllegalArgumentException: Document base D:\Program Files\Apache Software Foundation\Tomcat5.0\webapps\tomcat-docs does not exist or is not a readable directory ``` **问题描述:** 该错误表明...

Global site tag (gtag.js) - Google Analytics