IN and BETWEEN

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. Repeated OR conditions can be hard to read. IN expresses a small list of allowed values; BETWEEN expresses a range with both endpoints included.

Run the example

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (1, 2, 3)
ORDER BY OrderID;

Read it step by step

1. OrderID IN (1, 2, 3) means OrderID = 1 OR OrderID = 2 OR OrderID = 3.
2. The SELECT list returns each chosen order’s ID and date. ORDER BY OrderID displays them in ID order.
3. For a range, CustomerID BETWEEN 3 AND 5 means CustomerID >= 3 AND CustomerID <= 5. Both 3 and 5 qualify.

Check the result

The output has exactly three rows because those three OrderID values exist. IN does not return one row per listed value if a value is absent, and it does not duplicate an order when a value is listed twice.

Practice

Find customers whose CustomerID is between 3 and 5, inclusive. Which IDs appear?

SELECT CustomerID, FirstName FROM dbo.Customers WHERE CustomerID BETWEEN 3 AND 5 ORDER BY CustomerID;