Geek Answers Handbook

SQL: BETWEEN and IN (which is faster)

Possible duplicate:
Is there a performance difference between BETWEEN and IN with MySQL or SQL in general?

If I have the following identifiers:

1,2,3,4,5,6

Is it better to use IN or the inter clause to remove?

+5
source share
5 answers
  • If your identifiers are always consistent, you should use BETWEEN.
  • If your identifiers may or may not be sequential, use IN.

Efficiency should not be the deciding factor here. Having said that, BETWEEN seems to be faster in all the examples I tested. For instance:

, , x = 1:

SELECT COUNT(*) FROM table1 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.55s

SELECT COUNT(*) FROM table1 WHERE x BETWEEN 1 AND 6;
Time taken: 0.54s

, x :

SELECT COUNT(*) FROM table1 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.65s

SELECT COUNT(*) FROM table1 WHERE x BETWEEN 1 AND 6;
Time taken: 0.36s

, id . , .

SELECT COUNT(*) FROM table2 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.00s

SELECT COUNT(*) FROM table2 WHERE x BETWEEN 1 AND 6;
Time taken: 0.00s

, , SQL, . , , .

. SQL Server Express 2008 R2. .

+9

IN 1 or 2 or 3 or 4 or 5

>= 1 and <= 6

, .

+4

, . , . SQL .

+2

, , Id .

+1

Meanwhile, faster due to smaller comparisons. With the condition IN, each element passes each time.

But the goal of both is different:

  • Between is used when you are comparing a range of values ​​in some form to a sequence.

  • IN is used when comparing values ​​that are not in the Sequence.

0
source

More articles:


All Articles