SQLite BETWEEN and IN

BETWEEN and IN are shorthand WHERE conditions that make queries cleaner and easier to read. BETWEEN checks if a value falls within a range. IN checks if a value matches any item in a list.

Sample Table

products
────────────────────────────────────────────────────────
id │ name          │ category    │ price  │ rating
───┼───────────────┼─────────────┼────────┼───────
1  │ Notebook      │ Stationery  │ 2.99   │ 4.2
2  │ Pen           │ Stationery  │ 0.99   │ 4.5
3  │ Backpack      │ Bags        │ 24.99  │ 4.7
4  │ Water Bottle  │ Accessories │ 8.50   │ 3.9
5  │ Eraser        │ Stationery  │ 0.59   │ 4.0
6  │ Laptop Bag    │ Bags        │ 34.99  │ 4.6
7  │ Sticky Notes  │ Stationery  │ 1.99   │ 3.7

BETWEEN — Range Checking

-- Syntax
WHERE column BETWEEN low AND high

-- BETWEEN is inclusive — both endpoints are included

Numeric Range

-- Products priced from $1.00 to $10.00 inclusive
SELECT name, price FROM products
WHERE price BETWEEN 1.00 AND 10.00;

name          │ price
──────────────┼───────
Notebook      │ 2.99
Water Bottle  │ 8.50
Sticky Notes  │ 1.99

-- Equivalent to:
WHERE price >= 1.00 AND price <= 10.00

NOT BETWEEN

-- Products outside the $1 to $10 range
SELECT name, price FROM products
WHERE price NOT BETWEEN 1.00 AND 10.00;

name        │ price
────────────┼───────
Pen         │ 0.99
Eraser      │ 0.59
Backpack    │ 24.99
Laptop Bag  │ 34.99

BETWEEN with Text (Alphabetical Range)

-- Products whose names fall between 'E' and 'P' alphabetically
SELECT name FROM products
WHERE name BETWEEN 'E' AND 'P';

name
──────────────
Notebook
Pen
Laptop Bag   ← 'L' falls between 'E' and 'P'
Eraser       ← 'E' matches exactly (inclusive)

BETWEEN with Dates

-- Orders placed in Q1 2024
SELECT id, order_date FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

IN — List Matching

-- Syntax
WHERE column IN (value1, value2, value3, ...)

Match a List of Values

-- Products in Bags or Accessories category
SELECT name, category FROM products
WHERE category IN ('Bags', 'Accessories');

name          │ category
──────────────┼────────────
Backpack      │ Bags
Water Bottle  │ Accessories
Laptop Bag    │ Bags

-- Equivalent to:
WHERE category = 'Bags' OR category = 'Accessories'

NOT IN

-- All products NOT in Stationery
SELECT name, category FROM products
WHERE category NOT IN ('Stationery');

name          │ category
──────────────┼────────────
Backpack      │ Bags
Water Bottle  │ Accessories
Laptop Bag    │ Bags

IN with Specific IDs

SELECT name FROM products WHERE id IN (1, 3, 6);

name
───────────
Notebook
Backpack
Laptop Bag

IN with Subquery

The list inside IN can come from another SELECT query. This connects two tables without writing a JOIN.

-- Find products ordered by customer "Alice"
SELECT name FROM products
WHERE id IN (
  SELECT product_id FROM orders WHERE customer = 'Alice'
);

BETWEEN vs IN — When to Use Which

USE BETWEEN FOR:              USE IN FOR:
──────────────────────────    ─────────────────────────────
Continuous ranges             Specific discrete values
WHERE age BETWEEN 18 AND 65   WHERE dept IN ('HR','Finance')
WHERE price BETWEEN 5 AND 20  WHERE id IN (2, 5, 9)
WHERE date BETWEEN '24-01-01' WHERE status IN ('done','paid')
  AND '24-12-31'

Combining BETWEEN and IN

-- Stationery or Bags items priced between $1 and $30
SELECT name, category, price FROM products
WHERE category IN ('Stationery', 'Bags')
  AND price BETWEEN 1.00 AND 30.00;

name       │ category   │ price
───────────┼────────────┼───────
Notebook   │ Stationery │ 2.99
Backpack   │ Bags       │ 24.99
Sticky Notes│ Stationery│ 1.99

Key Takeaways

• BETWEEN low AND high is inclusive — both endpoints match
• NOT BETWEEN excludes the range
• IN (v1, v2, v3) matches any value in the list
• NOT IN excludes all values in the list
• IN works with subqueries, letting you build dynamic lists from another table