Question For All You Database Gurus (and Tinkerers) Out There

[tcepsa]

What are the advantages/disadvantages of using EXISTS vs. IN?

As an example, is it better to do something like

SELECT * FROM MainTable WHERE MainTable.categorynum IN (SELECT num FROM Categories WHERE category = 'Real')

Or

SELECT * FROM MainTable WHERE EXISTS (SELECT 1 FROM Categories WHERE MainTable.categorynum = Categories.num AND Categories.category = 'Real')

As far as I can tell, these two queries do very very similar things and I'm wondering what, if any, difference there is (are there certain situations whether it is better to use one or the other?)

[EDIT] Changed the queries so they'd be a little less contrived ;)

Comments

[shadeofnight.livejournal.com]

I think you would be better off doing a join, as that will do the same thing as the "in" or the "exists" and put less stress on the system.

I do not know what database this is, but a lot of them also have a limit to the number of values you can put in an "in" clause (Oracle gives an error when the number is over 1000).

Since I am guessing this is primary key to foreign key the join should cost almost nothing in system resources (even if it is not, it will still take less resource to do the join select, than the IN in my experense)

Just my 2 cents :)

[shadeofnight.livejournal.com]

As for the exist vs the in, I have no proff as I have not used the exist very much, but I belive it should be faster. Might be a fun test to build. :)

Just make your two tables, and run 1000 tests on each and see which ones take longer ^_^

[princesskaite.livejournal.com]

I'm not sure about exists vs in but a Join is one of the most expensive database ops.

[tcepsa.livejournal.com]

That inner join idea might work, though I'm not sure how to use it for multiple tables in the same query. As an example, say I want all of the records from the MainTable where the categorynum corresponds to the 'Real' category and the statusnum corresponds to the 'Active' status.

Would that be something like:
SELECT * FROM MainTable
INNER JOIN Categories ON MainTable.categorynum = Categories.num
INNER JOIN Statuses ON MainTable.statusnum = Statuses.num
WHERE Categories.category = 'Real' AND Statuses.status = 'Active'

(I'm currently running against an Oracle database)

[shadeofnight.livejournal.com]

As long as you only want the ones that match both, then yes...

select M.* from mainTable M, Categories C, Statuses S
where M.categorynum = C.categorynum
and M.statusnum = S.statusnum
and C.category='Real' and S.status = 'Active'

You do not need to state inner joins in oracle as they are implied, you only need to state outerjoins.

You can also aliast a table name to something else easler to type (like I did above). It is also an easy way to have a table link to itself (with aliases).

[shadeofnight.livejournal.com]

For more info on the "in" vs "exists" :

From the article (on SQL server, but the same logic will work with oracle as well) Optimization of SQL statements

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. [6.5, 7.0, 2000] Updated 10-30-2003

As a side note, if the categorynum and statusnum are known ahead of time, and do not change, you can remove the need to do any joins.

As for which is faster.....

It will all depend on how the indexes are set up, and how many values are in all of the tables.

I am getting the feeling you would be better off just coding in the Categoriesnum and statusnum in the query and not doing a join at all, and just doing the two equal calls in the where statement

[fortryll.livejournal.com]

uhm...

uhm...

~thinks~

can we just go get ice cream now? my brain hurts!

[tcepsa.livejournal.com]

Ooh, ice cream! Let's go! ^_^