SQL Optimization Case Study - JOIN Clause vs. IN Clause

Sometimes you just need to use IN, specifically if there are some ids in ColdFusion that need to be part of the query. This could happen for example when you have results from search server but need to filter out them based on some SQL based logic.

When that happens one thing to check is that number of ids never exeed capability of database. Oracle 9 "in list iterator" can handle 1000 items, I haven't checked this with other databases.

If you anticipate having more than that it's relatively easy to build code that splits list to serveral parts.

Of course if you run into this kind of situation it's always good idea to really think if there is some other way - it will never be really efficient.

Hi Ben,

you might want to check the EXISTS clause, too. E.g.

SELECT requestID, request_title
FROM Request r
WHERE EXISTS
(
SELECT *
FROM User
WHERE userID=r.userID
)

Given proper indexes, EXISTS is much faster than IN. And, considering that NOT IN is even slower than IN, NOT EXISTS can really bring dramatic performance improvements.

You still have to keep in mind that different database engines handle things differently. For example, with MSSQL (2000) it is generally better to use SELECT * in the subquery (instead of SELECT fieldname), because the query optimizer will choose the best index or combination of indexes.

Best,

Chris

@Ben,

yes, EXISTS can reference values from the outer select.
Look at the condition of the subquery from my example:

WHERE userID=r.userID

Here r is an alias for the table from the outer select. The subquery only selects those records that have a corresponding userID in the outer select. Those records that do NOT have a matching userID are not returned.

And, you are understanding EXISTS quite correctly. EXISTS evaluates to true if a matching record is found.

And, EXISTS indeed evaluates per row. Thus, a record (from the outer select) that has a corresponding record in the sub query is returned in your result set, whereas a record, that does not have a match in the subquery, is not returned.

And, because of the matching WHERE clause in the subquery, the subquery.recordCount can only be 1 or 0. And that is pretty much like CFML: recordcount = 0 = FALSE, recordcount >0 = TRUE

So the following statements are equal in result (compared to my last example, I switched tables here... makes more sense to me ;-)):

-- Select all users that have entries in request table
SELECT u.userID, u.lastname, u.firstname
FROM User u
WHERE userID IN (SELECT userID FROM Request)

-- again, select all users that have entries in request table
SELECT u.userID, u.lastname, u.firstname
FROM User u
WHERE EXISTS
(
SELECT *
FROM Request
WHERE userID=u.userID
)

Plus, you could use a join, too:

SELECT DISTINCT u.userID, u.lastname, u.firstname
FROM User u, Request r
WHERE r.userID=u.userID

But, note the use of DISTINCT here. Without it, you will get multiple records for users with more than 1 request. The cost of DISTINCT is pretty high, so that alternative is usually slower than EXISTS.

Side note: all those variants can be used to return only those records without a match, too.

Chris

Man, use .NET technologies to connect to your Data Base, and everything gonna be allright!

hei.

interesting discussion. :)
found it quite helpful for the reports i need to generate. thanks!

joan

Interestingly I've had the same experience. I used subselects with IN statements thinking it would be much faster because in my case the outer table jas to be joined 3 times and i was thinking it'll scan the table 3 times.
It was something like this;
select name from names where id in ( creating a list of ids with simple select from another table)
It took minutes.
I changed this to
select n1.name, n2.name, n3.name from names n1, n2, n3, othertable ot where
n1.id=*ot.f1 and n2.id=*ot.f2 and n3.id=*ot.f3

The join runs way faster then first version...

You should be the no.1 google hit, not no. 2. Hopefully since I'm spending more time on your site, you'll be no. 1 for this topic.

I've always done Joins, so it is second-nature to me. However, I wanted to understand which is the recommended best-method. In order to think out of the box, and see what has been tested, I was wondering if IN clause would work better - apparently not. Thanks for the post.

IN vs. EXISTS performance can vary depending on the size and indexing of both the outer and inner tables. Also a neat behavior of EXISTS is that it only needs to find one row that matches then it can stop looking. For example, this will return a 0 or 1 (in Oracle - good "row_exists" function):

select count(*)
from dual where exists (select null from t1 where ...)

I was with the belief that an IN condition is better than a JOIN condition. But today, I came across a scenario where the JOIN condition performed better than IN.

To add to my woes, the IN condition and the JOIN condition both retrieve only 9 rows.

The cost of the query where IN was used is less than the Cost of the query where the JOIN is used.

Can anyone explain how this is possible?

Hi Ben.

I am confused in using Inner JOin and Outer join.
My sql queries having many tables references. So i have used LEFT OUTER JOIN.
Still my query performance is slow.
Any idea to speed up my queries?

Thanks.

Hi, Ben,
I still a bit confused here...
I did too find that at one times that in is faster than join, and visa versa.
So, have you found any based that when is in is faster and when is the oposite?