SQL IN and BETWEEN Operators

The SQL IN operator checks whether a value matches any value in a list, and BETWEEN checks whether a value falls inside a range. Both make WHERE clauses shorter and easier to read than long chains of OR and AND.

The IN Operator

Instead of Country = 'UK' OR Country = 'USA' OR Country = 'Canada', write one IN list.

SELECT CustomerName, Country
FROM Customers
WHERE Country IN ('UK', 'USA', 'Canada');

CustomerName  | Country
--------------+--------
Cedar Market  | UK
Fresh Mart    | UK
Harbor Goods  | USA
Indigo Retail | USA
Jade Imports  | Canada
(5 rows)

NOT IN

NOT IN returns rows whose value is not in the list.

SELECT ProductName, Category
FROM Products
WHERE Category NOT IN ('Beverages', 'Snacks');

ProductName  | Category
-------------+-----------
Basmati Rice | Grains
Olive Oil    | Oils
Wheat Flour  | Grains
Honey        | Sweeteners
(4 rows)

The BETWEEN Operator

BETWEEN is inclusive: both end values are part of the range. This finds products priced from 8 to 15 inclusive.

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 8 AND 15
ORDER BY Price;

ProductName  | Price
-------------+------
Wheat Flour  | 8
Honey        | 11.5
Basmati Rice | 12.5
Almonds      | 15
(4 rows)

BETWEEN with Dates

Find orders placed in the first quarter of 2026. Because our dates are YYYY-MM-DD text, string ranges work correctly. With DATETIME columns, be careful: BETWEEN '2026-01-01' AND '2026-03-31' excludes times after midnight on 31 March, so many developers prefer >= start AND < next_day.

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-03-31';

OrderID | OrderDate
--------+-----------
101     | 2026-01-05
102     | 2026-01-09
103     | 2026-02-11
104     | 2026-02-14
105     | 2026-03-02
106     | 2026-03-20
(6 rows)

IN with a Subquery

The list for IN can come from another query. This shows customers who have placed at least one order. Subqueries get their own chapter later.

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

CustomerName
--------------
Alpha Traders
Blue Sky Foods
Cedar Market
Evergreen Co
Golden Grains
Harbor Goods
Indigo Retail
(7 rows)

Frequently asked questions

Is SQL BETWEEN inclusive?

Yes. BETWEEN a AND b is the same as >= a AND <= b, so both boundaries are included.

Why does NOT IN return no rows when the list contains NULL?

x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the comparison with NULL is unknown, so no row qualifies. Use NOT EXISTS or filter out NULLs in the subquery.

Is IN faster than multiple OR conditions?

Most databases treat them the same internally. IN is mainly better for readability.