NOT IN vs. <> 1

[kermitforney]

Anyone have any info or a link to an article that explains best practices or a comparison for NOT IN and <> (not equal to)??

Thanks!! D

 

[RiverGuy]

Well, they are completely different. NOT IN compares against a list of values whereas <> compares one value versus another single value.

 

[kermitforney]

Well, I am asking because I have a query and I am gauging performance. The end of my WHERE clause criteria is, "AND table.column NOT IN (56)". I have been testing the speed and using NOT IN instead of "<> 56" is much faster. The results for NOT IN are very consistent while the results for <> are all across the board. Not sure why this is, but it is. /

 

[RiverGuy]

I've never seen that behavior personally. Do they produce the same execution plans?

 

[kermitforney]

Same exact plans. Weird . . thinking it may be order precedence and decreasing the size of a result set. I dunno, but i do know I have to get back to work and stop thinking about this. )

Thanks, River!