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]

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).