2

Today I heard that a query with <> will take more time to execute than one with not in. I tried to test this and with an equal plan had the following time results:

select * from test_table where test <> 'test' 0,063 seconds select * from test_table where test not in ('test') 0,073 seconds

So the question is, what is the difference between <> and not in for a single condition and what is better to use.

Vincent
  • 1,459
  • 15
  • 37
Dmitry.Samborskyi
  • 485
  • 2
  • 10

1 Answers1

5

Whether or not the column is indexed, I would expect both queries to perform a full scan on the table, i.e the query plan is essentially the same. The small timing difference you noted is probably insignificant - run the same query more than once and you will get different timings.

Having said that I would use <> because it is more natural.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • But realy, "not in" expect list, even if it is a list of one element, opposite <> expect one element. Is there realy nothing special between two of this ?maybe in other case of table structure – Dmitry.Samborskyi Aug 27 '15 at 12:54
  • If the query plans are the same (and they are if you check) then there is no performance difference between the two methods. – Tony Andrews Aug 27 '15 at 13:12
  • You might also check the query plans to see if a transformation has been applied to turn them both into the same SQL behind the scenes anyway. It would appear on one of the lines under "Predicate Information (identified by operation id):". If a transformation has been applied then that should be conclusive, and would explain why the cost is the same. Then you can take them both to your DBA and innocently ask him to explain ;) – David Aldridge Aug 27 '15 at 13:21