SQL IN and BETWEEN: lists and ranges

When a WHERE condition starts piling up ORs (“Lyon or Marseille or Nantes…”) or framing a value (“between 10 and 50”), two operators make the query shorter and easier to read: IN and BETWEEN.

Syntax

WHERE column IN (value1, value2, …)
WHERE column BETWEEN minimum AND maximum

How it works

IN: one value from a list

ville IN ('Lyon', 'Marseille') is strictly equivalent to ville = 'Lyon' OR ville = 'Marseille'. The list can hold as many values as needed, and can even be replaced by a subquery that returns a list.

NOT IN does the opposite: it keeps the rows whose value matches none of the listed values.

BETWEEN: a range

prix BETWEEN 10 AND 50 is equivalent to prix >= 10 AND prix <= 50: both bounds are included. It also works with dates written as YYYY-MM-DD and with text (alphabetical order).

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT prenom, nom, ville
FROM clients
WHERE ville IN ('Lyon', 'Marseille')
LIMIT 8;

SELECT nom, prix
FROM produits
WHERE prix BETWEEN 10 AND 20;

SELECT id, date_commande, statut
FROM commandes
WHERE date_commande BETWEEN '2025-01-01' AND '2025-01-31'
LIMIT 8;

SELECT statut, COUNT(*) AS nb
FROM commandes
WHERE statut NOT IN ('livrée', 'expédiée')
GROUP BY statut;

Common mistakes

SELECT * FROM produits WHERE prix BETWEEN 50 AND 10;

SELECT * FROM produits WHERE prix BETWEEN 10 AND 50;

SELECT * FROM clients WHERE ville IN 'Lyon', 'Lille';

SELECT * FROM clients WHERE ville IN ('Lyon', 'Lille');

SELECT * FROM clients WHERE age NOT IN (20, NULL);

SELECT * FROM clients WHERE age NOT IN (20) AND age IS NOT NULL;

Exercises

Try writing the query yourself before opening the solution.

SELECT nom, categorie, prix FROM produits WHERE categorie IN ('Livres', 'Alimentation');

SELECT prenom, nom, salaire FROM employes WHERE salaire BETWEEN 3000 AND 4000;

Frequently asked questions