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

[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