Forum

Posted by Yogita, Nov. 11, 2021, 9:01 p.m.

Does "IN" operator perform better than an Inner join ?

Does "IN" operator perform better than an Inner join ?

Answers

If you're specifying the set manually (ie, WHERE column IN (a, b, c, d)), this is often fairly quick. If you have a high cardinality (ie, many entries in the joining column), a JOIN typically works better. This is especially true for working with large datasets (think 100k+ rows).
Note that pure query performance isn't always what you're after - sometimes it's clarity of the solution and or the intent behind the query. Meaning, if I need to run a one off query every so often, it's often not worth the optimization time to figure out how to make it as fast as can be. Or if I want to show someone how to perform a query and let them reasonably modify it, the IN might work better. If it's a query that will be run every time a web page updates on a high traffic site, it's probably worth optimizing this (though there are other ways outside of SQL to do this too...)

Thanks Mike!!

Yogita, Nov. 18, 2021, 10:04 a.m.
SQLPad user avatar

Mike (228)

Nov. 12, 2021, 10:44 a.m.