SQL IN and BETWEEN Operators

IN checks whether a value is in a list, and BETWEEN checks whether it lies in a range. Both are shorter and clearer than chains of OR and AND, and both hide one surprise each: BETWEEN includes both ends, and NOT IN breaks when the list contains NULL.

Sample data

IN: match any value in a list

Orders from customers 1, 3 and 5:

SELECT order_id, customer_id, statusFROM ordersWHERE customer_id IN (1, 3, 5);

IN works with text too:

SELECT order_id, statusFROM ordersWHERE status IN ('Pending', 'Shipped');

NOT IN

SELECT product_name, categoryFROM productsWHERE category NOT IN ('Electronics', 'Stationery');

IN with a subquery

The list can come from another query. Customers who have at least one order:

SELECT customer_id, customer_nameFROM customersWHERE customer_id IN (SELECT customer_id FROM orders);

You will learn more in Subqueries.

The NOT IN and NULL trap

If the list contains a NULL, NOT IN returns no rows at all:

SELECT first_nameFROM employeesWHERE emp_id NOT IN (SELECT manager_id FROM employees);

Exclude the NULLs and the query finds the employees who manage nobody:

SELECT first_nameFROM employeesWHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

BETWEEN: a range

Products priced from 1,000 to 4,000:

SELECT product_name, priceFROM productsWHERE price BETWEEN 1000 AND 4000ORDER BY price;

BETWEEN with dates

Orders placed in February and March 2025:

SELECT order_id, order_dateFROM ordersWHERE order_date BETWEEN '2025-02-01' AND '2025-03-31';

NOT BETWEEN

SELECT first_name, salaryFROM employeesWHERE salary NOT BETWEEN 50000 AND 100000ORDER BY salary;

BETWEEN with text

BETWEEN also works on text, comparing alphabetically:

SELECT customer_nameFROM customersWHERE customer_name BETWEEN 'A' AND 'G'ORDER BY customer_name;

Note that a name like 'Gaurav' would not match, because 'Gaurav' sorts after 'G'. To include every name starting with G, use customer_name < 'H'.

SELECT first_name, salaryFROM employeesWHERE salary BETWEEN 45000 AND 75000ORDER BY salary DESC;

SELECT customer_name, cityFROM customersWHERE city IN ('Mumbai', 'Delhi', 'Goa');

IN and BETWEEN FAQ