SQL IN and BETWEEN Operators Explained for Beginners

The SQL IN operator checks whether a value matches any value in a list. The SQL BETWEEN operator checks whether a value falls inside a range. Both go in a WHERE clause, and both save you from writing long, messy conditions. This guide explains SQL IN and BETWEEN for beginners, with small examples you can copy and run.

You do not need any experience. We define each word the first time we use it. If you have not used WHERE yet, read the SQL SELECT and WHERE guide linked below first.

The example table we will use

A table is like a spreadsheet: it has columns (the headings) and rows (one record each). We will use a small table called products that stores items in a shop.

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  category VARCHAR(20),
  price DECIMAL(6,2),
  added_on DATE
);

INSERT INTO products VALUES
  (1, 'Notebook', 'Paper', 5.00, '2026-01-10'),
  (2, 'Pen', 'Writing', 1.50, '2026-02-03'),
  (3, 'Pencil', 'Writing', 0.80, '2026-02-20'),
  (4, 'Stapler', 'Office', 12.00, '2026-03-15'),
  (5, 'Backpack', 'Bags', 35.00, '2026-04-01'),
  (6, 'Marker', 'Writing', 2.50, '2026-05-11');

The first statement creates the table and names each column with a data type (INT is a whole number, VARCHAR is text, DECIMAL is a number with decimals, DATE is a calendar date). The second statement adds six rows. Each pair of brackets in the VALUES list is one row.

The problem: too many OR conditions

Say you want products from the Paper, Bags, or Office categories. With only WHERE and OR, you would write this:

SELECT name, category
FROM products
WHERE category = 'Paper'
   OR category = 'Bags'
   OR category = 'Office';

It works, but it gets long fast. SELECT picks the columns to show. FROM names the table. WHERE filters the rows. OR means a row is kept if any one of the conditions is true. Now let us make it shorter.

What is the SQL IN operator?

IN lets you give a list of allowed values inside brackets. A row is kept if the column matches any value in the list.

SELECT name, category
FROM products
WHERE category IN ('Paper', 'Bags', 'Office');

Line 1 chooses the name and category columns. Line 2 reads from products. Line 3 keeps rows where category is Paper, Bags, or Office. The result has three rows: Notebook, Stapler, and Backpack. It is the same as the OR version, but easier to read.

Text values go inside single quotes. Numbers do not need quotes:

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

This returns the rows with id 1, 3, and 5: Notebook, Pencil, and Backpack.

NOT IN: exclude values from a list

Put NOT in front of IN to do the opposite. You keep rows that do not match anything in the list.

SELECT name, category
FROM products
WHERE category NOT IN ('Writing');

This keeps every product whose category is not Writing. You get Notebook, Stapler, and Backpack. One warning: if the list contains NULL (SQL's word for a missing value), NOT IN may return no rows at all. Avoid NULL inside your lists.

What is the SQL BETWEEN operator?

BETWEEN checks whether a value sits inside a range, with both ends included. It works with numbers, dates, and text.

SELECT name, price
FROM products
WHERE price BETWEEN 1 AND 12;

This keeps products priced from 1 up to 12, including 1 and 12 themselves. You get Pen (1.50), Stapler (12.00), Notebook (5.00), and Marker (2.50). Pencil (0.80) is too cheap, and Backpack (35.00) is too expensive. The smaller number must come first, or you will get no rows.

BETWEEN is a short way to write this:

SELECT name, price
FROM products
WHERE price >= 1 AND price <= 12;

>= means greater than or equal to, and <= means less than or equal to. AND means both conditions must be true. The result is identical.

BETWEEN with dates

Dates are written in the YYYY-MM-DD format inside quotes. This finds products added in February and March 2026:

SELECT name, added_on
FROM products
WHERE added_on BETWEEN '2026-02-01' AND '2026-03-31';

It returns Pen, Pencil, and Stapler. Both end dates are included. Be careful if your column also stores a time of day: a value like 2026-03-31 14:00 is after midnight on March 31, so BETWEEN would leave it out. For columns with times, use >= and < with the next day instead.

NOT BETWEEN

Use NOT BETWEEN to find values outside a range.

SELECT name, price
FROM products
WHERE price NOT BETWEEN 1 AND 12;

This returns Pencil (0.80) and Backpack (35.00), the two prices outside the range.

Combine IN and BETWEEN

You can use both in one query with AND. Here we want Writing or Office items that cost between 1 and 20:

SELECT name, category, price
FROM products
WHERE category IN ('Writing', 'Office')
  AND price BETWEEN 1 AND 20;

The first condition keeps Pen, Pencil, Marker, and Stapler. The second removes Pencil because 0.80 is below 1. The final result is Pen, Marker, and Stapler.

IN vs BETWEEN: which one should you use?

A simple rule: if you can name each value, use IN. If you mean everything from here to there, use BETWEEN.

Common beginner mistakes

• Forgetting quotes around text values. Write 'Paper', not Paper.
• Putting the larger number first in BETWEEN. Write BETWEEN 1 AND 12, not BETWEEN 12 AND 1.
• Forgetting that BETWEEN includes both ends.
• Using BETWEEN on date-time columns and missing rows on the last day.
• Using NOT IN with a list that contains NULL.

Practice challenges

1. Show all products whose category is Writing or Bags.
2. Show products that cost between 2 and 10.
3. Show products added between 2026-01-01 and 2026-02-28.
4. Show products that are not in the Office or Paper categories.

Try each one yourself before looking at the next section. The answers use only what you learned above.