BETWEEN vs IN in SQL Server: Which One Reads Less?

BETWEEN vs IN looks like a style choice, and it is not. BETWEEN and the two comparison operators are the same predicate. An IN list is a different request, and SQL Server reads it differently.

Build the Test

The test of BETWEEN vs IN needs a table first and an index later. The demo creates a database named BetweenInDemo. Its InvoiceLines table holds 200,000 rows for 20,000 invoices, ten lines for each invoice. The primary key is the line number, so a search by invoice needs help from an index.

IF DB_ID(N'BetweenInDemo') IS NULL CREATE DATABASE BetweenInDemo;
GO
USE BetweenInDemo;
GO
DROP TABLE IF EXISTS dbo.InvoiceLines;
CREATE TABLE dbo.InvoiceLines (
    LineID    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    InvoiceID int           NOT NULL,
    Quantity  int           NOT NULL,
    Amount    decimal(10,2) NOT NULL
);
WITH n AS (
    SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS k
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.InvoiceLines (InvoiceID, Quantity, Amount)
SELECT (k - 1) / 10 + 1, k % 7 + 1, 5 + k % 50 FROM n;
GO
CREATE OR ALTER FUNCTION dbo.CachedRuns ()
RETURNS TABLE
AS RETURN
SELECT cp.objtype AS PlanType, cp.usecounts AS UseCount, qs.last_logical_reads AS LogicalReads, LEFT(st.text, 120) AS Statement
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID()
  AND st.text LIKE N'%InvoiceLines%' AND st.text NOT LIKE N'%CachedRuns%';

The helper function reads the plan cache for the current database. For each cached plan it shows the plan type and the number of uses. It also shows the logical reads of the last run. A fair test asks the same question three ways: invoices 20 to 40, which is 21 invoices and 210 lines. Each query sits in its own batch.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID >= 20 AND InvoiceID <= 40;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID BETWEEN 20 AND 40;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID IN (20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40);
GO
SELECT PlanType, UseCount, LogicalReads, Statement FROM dbo.CachedRuns() ORDER BY PlanType DESC;

Two cache rows serve three queries. SQL Server replaced the constants in BETWEEN and in the two operators with parameters. Both statements became the same text, so they share one prepared plan, and that plan ran twice. The IN list is a separate ad hoc plan. With no index on the search column, every query scans the table. All of them read the same 748 pages.

Add an Index and Test Again

An index on InvoiceID lets SQL Server jump to the invoices it needs. The next script creates it and repeats the three queries.

CREATE INDEX IX_InvoiceLines_Invoice ON dbo.InvoiceLines (InvoiceID) INCLUDE (Quantity, Amount);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID >= 20 AND InvoiceID <= 40;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID BETWEEN 20 AND 40;
GO
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID IN (20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40);
GO
SELECT PlanType, UseCount, LogicalReads, Statement FROM dbo.CachedRuns() ORDER BY PlanType DESC;

Now the winner is clear. The shared plan of BETWEEN and the operators finds the start of the range once. Then it reads forward, so it reads 4 pages. The IN list seeks 21 times, once for each value, and reads 64 pages. SET STATISTICS IO shows the same thing as a scan count of 21 against a scan count of 1. The setting is easy to keep on, as STATISTICS TIME and IO: Turn Them On for Every SSMS Query shows.

Make the List Longer

The gap grows with the list. The script below builds an IN list of 2,000 values with dynamic SQL and compares it with the equivalent BETWEEN.

SET NOCOUNT ON;
DECLARE @list nvarchar(max) = (
    SELECT STRING_AGG(CAST(n AS nvarchar(max)), N',')
    FROM (SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 999 AS n
          FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x);
DECLARE @sql nvarchar(max) = N'SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID IN (' + @list + N');';
SET STATISTICS IO ON;
EXEC (@sql);
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID BETWEEN 1000 AND 2999;
SET STATISTICS IO OFF;

Both queries return the same 20,000 lines. The IN list needs 2,000 seeks and 6,476 reads. Compiling it costs CPU too, about 140 ms in this run. The BETWEEN query reads 71 pages. For a contiguous range, BETWEEN wins by a wide margin.

When IN Is the Right Question

The comparison changes when the values are not next to each other. BETWEEN returns everything between the two ends. IN returns only the values you name. Those are different questions, and they have different answers.

SET STATISTICS IO ON;
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID IN (20, 10000, 19000);
SELECT COUNT(*) AS Lines FROM dbo.InvoiceLines WHERE InvoiceID BETWEEN 20 AND 19000;
SET STATISTICS IO OFF;

The IN list reads 9 pages because it returns 30 lines. The BETWEEN query reads 640 pages because it returns 189,810. Comparing their reads proves nothing, since the two queries do not ask the same thing. Choose IN when you mean three values and BETWEEN when you mean a range.

BETWEEN Includes Both Ends

One more difference matters for dates. BETWEEN includes the upper bound, and a date alone means midnight. A month that ends on January 31 then misses every row stamped later that day. The script below loads four shipments and counts January twice.

DROP TABLE IF EXISTS dbo.Shipments;
CREATE TABLE dbo.Shipments (ShipmentID int NOT NULL PRIMARY KEY, ShippedAt datetime NOT NULL);
INSERT INTO dbo.Shipments VALUES (1, '20260115 09:00'), (2, '20260131 00:00'), (3, '20260131 14:30'), (4, '20260201 00:00');
GO
SELECT COUNT(*) AS WithBetween FROM dbo.Shipments WHERE ShippedAt BETWEEN '20260101' AND '20260131';
SELECT COUNT(*) AS WithHalfOpenRange FROM dbo.Shipments WHERE ShippedAt >= '20260101' AND ShippedAt < '20260201';

BETWEEN misses shipment 3, which left at 14:30 on January 31. The half-open range, with a lower bound included and an upper bound excluded, counts it and leaves out February 1. For dates and times, write the range with two operators.

What to Remember

BETWEEN vs IN is a question of meaning first and speed second. BETWEEN is the same predicate as two operators, so any difference between those two is noise. They even share one cached plan. An IN list costs one seek per value, which is cheap for three values and expensive for 2,000. A test is only fair when every query uses the same bounds and returns the same rows. Without an index, all of them scan the table.

You could argue that the IN list is easier to read when the values are not a range. It is, and readability counts. Use it for values that are separate. When you finish the demo, drop the database.

USE master;
GO
ALTER DATABASE BetweenInDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BetweenInDemo;

A faster query is not the one that reads less, it is the one that answers the same question.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.