IN and EXISTS keywords

[masterchicker]

What is the difference between the IN and EXISTS keywords. What kind of situations should I use each keyword to attain optimal performance? thanks.

 

[donutman]

Here's a thread that shows how well the Exists clause works. thread183-969463.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Lothario]

I learned this from BOL...

When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster.

May be some experts here can explain it more...

-L

 

[masterchicker]

Hmmm....

This is the original code:

SELECT col1, col2, col3, col4 FROM Table_1
WHERE co1 IN
(
SELECT col1 FROM Table2
)

-- Only col1 records that were returned from the query of Table2 will be returned.


When I do this:

SELECT col1, col2, col3, col4 FROM Table_1
WHERE EXISTS
(
SELECT col1 FROM Table2
)

It seems that it did not filter the query statement.

EXISTS returns true even if one record in table2 is returned.


I want to return the same results but more efficient. I am receiving a SERVER TIME OUT error in my asp code and I suspect my stored procedure is causing the problem. That is why I want my code to be more efficient.. Please advise. Thanks.

 

[donutman]

First, you need to index both tables on Col1 in order to obtain the best performance, however, there may be other reasons not to index Col1. It depends upon other factors about the project.
Second, the first query you posted would likely be very inefficient. An inner join would be better.

[Blue]SELECT[/Blue] col1[Gray],[/Gray] col2[Gray],[/Gray] col3[Gray],[/Gray] col4 
   [Blue]FROM[/Blue] Table_1 T1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] Table_2 T2
   [Blue]ON[/Blue] [Gray]([/Gray]T1.col1[Gray]=[/Gray]T2.col1[Gray])[/Gray]

Third, your last query should be reworked as:

[Blue]SELECT[/Blue] col1[Gray],[/Gray] col2[Gray],[/Gray] col3[Gray],[/Gray] col4 
   [Blue]FROM[/Blue] Table_1 T1 [Blue]WHERE[/Blue] exists
      [Gray]([/Gray][Blue]SELECT[/Blue] col1 [Blue]FROM[/Blue] Table_2 T2 
          [Blue]WHERE[/Blue] T1.col1[Gray]=[/Gray]T2.col1[Gray])[/Gray]

Finally, of the two above queries, I believe the latter will perform faster. The reason the textbooks (and experience) prefers the Exists clause is because it only takes one row within that subquery to trigger the end of that query. It need not find all of the matches on col1 for the Exists clause to succeed. It's like saying is X or Y true, if you know X is true than you don't have to check Y in order to conclude that the expression (X or Y) is true.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[masterchicker]

When I use EXISTS, it will execute:

SELECT col1, col2, col3, col4 FROM Table_1

You are right, only one record from (SELECT col1 FROM Table2) will make it true. But only the records that are retrieved from (SELECT col1 FROM Table2) should be retrieved by (SELECT col1, col2, col3, col4 FROM Table_1). That is why I use IN.

I will try the INNER JOIN though. The reason why I did not use INNER JOIN in the first place is because the code is really complex, composed of even more JOIN's. Thanks for your replies. I'll post again if INNER JOIN works.