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

[princesskaite.livejournal.com]

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