مقایسه تفاوت های Differences Between Sql Exists And In

DifferenceBetween · #1 06-27-2013

Sql Exists vs In

Problem solving in TSQL is available through different methods and the desired results can be seen using any of them. One of the ways the end results can be achieved is through use of IN and EXISTS clauses. The use of the clauses helps filtration of the result set with reference to a subquery that is available. The execution of IN and EXISTS slightly differs and it is these differences that are evaluated.

There are various factors that determine whether or not IN or EXISTS will be rolled out. One of them is the data amount available in the table. A huge amount of data results in the SQL server returning to use of an index scan rather than using an Index Seek.

Differences

The statistics given also tell a lot about the execution plan that ought to be followed. The difference is shown when the server has accumulated enough statistics to make a valid decision and when there are no statistics the first time. The hardware in use also determines whether IN or EXISTS will be employed. This is largely dependent on the number of CPU’s available.

EXISTS are run when there is need to match results of a query with another subquery. IN on the other hand is used when retrieving the values of specific columns that lie within a list. The decision of which to use is solely based by appropriateness, i.e. when you feel that its use is appropriate.

In the event that a subquery is used and a null value is returned, the entire statement becomes NULL. This in effect points to the use of EXISTS keyword. The use of the IN keyword comes about when a comparison of various values in subqueries is required. The EXISTS keyword is mainly used in evaluation of true or false statements and IN is used in most corresponding subquery statements.

In general, EXISTS will be faster than IN, the reason being that when EXISTS are run, the search has found a hit and will be reviewing whether the condition has proved to be true. When running IN, it collects all results from the subquery and presents them for further processing, and this process takes a while.

It is important to note that phrasing of the query must be done right and be checked prior to being run. Failure to ensure that the query is correct results to EXISTS and IN presenting different values, and this is not the aim of their use in SQL server. The optimizer must always be optimal when working as it should.

Summary

Problem solving in TSQL is commonly done with EXISTS and IN.

Slight differences are observed when rolling out EXISTS and IN, though optimization for each should give the same value.

Statistics is one of the determinants of whether EXISTS or IN will be rolled out

Hardware in use is also critical in determining if EXISTS or IN is to be deployed

The running of EXISTS will be useful when there is need to correlate the results of a given query with another subquery. EXISTS is also very common when true or false statements need evaluation.

IN is commonly used when there is a need to retrieve specific columns in a list. Also it is commonly used when value comparison in subqueries is required.

EXISTS is generally faster than IN as it runs, finds a hit, and reviews whether the condition has proved to be true

IN is slower as it collects all results from the subquery and processes it.

Although different executions are present, optimization should give similar results in EXISTS and IN.

You might also like…