As your WHERE clauses grow, they can turn into long chains of OR and AND. Two operators, SQL IN and BETWEEN, make the most common cases shorter and easier to read: IN checks whether a value is in a list, and BETWEEN checks whether it falls inside a range. Both work the same way in SQLite, MySQL, PostgreSQL and SQL Server.
IN: match any value in a list
Instead of writing State = 'Kerala' OR State = 'Karnataka' OR State = 'Tamil Nadu', put the values in brackets after IN:
SELECT CustomerName, City, State
FROM Customers
WHERE State IN ('Kerala', 'Karnataka', 'Tamil Nadu')
ORDER BY State, City;Returns 6 rows: two in Karnataka, one in Kerala and three in Tamil Nadu.
The result is exactly the same as the OR version, but the query is shorter and adding a fourth state means adding one value, not a whole condition. The list can hold text, numbers or dates, as long as they match the column’s type: WHERE CategoryID IN (1, 3) finds beverages and dairy products.
IN is common in web apps. When a shopper ticks three categories in a filter panel, the page sends their IDs and the query uses WHERE CategoryID IN (?, ?, ?), with one placeholder per ticked box. The values themselves are passed separately through a prepared statement, never pasted into the SQL text.
NOT IN
NOT IN keeps rows whose value is not in the list, for example WHERE Status NOT IN ('Delivered', 'Cancelled') for orders that still need attention. As with other comparisons, rows where the column is NULL are left out of both IN and NOT IN results.
IN with a subquery
The list after IN does not have to be typed by hand. It can come from another query, called a subquery. The inner query runs first and produces a list of IDs; the outer query then uses that list. This query finds customers who have an order on its way:
SELECT CustomerName, City
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
WHERE Status = 'Shipped'
);Returns 2 rows: Coimbatore Mart and Jasmine Mini Mart.
The subquery must return a single column. You could get the same answer with a join from SQL Joins, but IN has one advantage: a customer with two shipped orders still appears only once, with no need for DISTINCT.
Watch out: Be careful with NOT IN and a subquery that can return NULL. WHERE EmployeeID NOT IN (SELECT ManagerID FROM Employees) returns no rows at all, because Karthik’s ManagerID is NULL and SQL cannot be sure any value is “not equal” to an unknown. Filter the NULLs out inside the subquery, as in the next example, or use NOT EXISTS.
SELECT EmployeeID, FirstName, Department
FROM Employees
WHERE EmployeeID NOT IN (
SELECT ManagerID
FROM Employees
WHERE ManagerID IS NOT NULL
);Returns 5 rows: Vignesh, Sneha, Nisha, Ajay and Fathima. Remove the inner WHERE line to see the NULL trap return 0 rows.
BETWEEN: values in a range
BETWEEN low AND high is a short way to write value >= low AND value <= high. Notice the >= and <=: both ends are included. Write the smaller value first; BETWEEN 150 AND 90 matches nothing.
SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 90 AND 150
ORDER BY Price;Returns 6 rows from Murukku (90) to Masala Tea (150) – both end values are included.
BETWEEN with dates
Because our dates are stored as YYYY-MM-DD text, BETWEEN works for date ranges too. This finds every order placed in February 2026:
SELECT OrderID, OrderDate, Status
FROM Orders
WHERE OrderDate BETWEEN '2026-02-01' AND '2026-02-28'
ORDER BY OrderDate;Returns 3 rows: orders 1004, 1005 and 1006.
Tip: Take care when a column also stores a time, such as a MySQL DATETIME value like '2026-02-28 18:30:00'. BETWEEN '2026-02-01' AND '2026-02-28' stops at midnight at the start of 28 February and misses that order. A safer pattern for any date range is OrderDate >= '2026-02-01' AND OrderDate < '2026-03-01'.
NOT BETWEEN and text ranges
NOT BETWEEN keeps values outside the range, so Salary NOT BETWEEN 50000 AND 80000 finds the lowest and highest earners. BETWEEN also works on text in alphabetical order: CustomerName BETWEEN 'A' AND 'D' matches names from A up to exactly “D” – but not “Deccan Traders”, which sorts after “D”. Text ranges are easy to get wrong, so prefer LIKE or < comparisons for them.
Choosing between SQL IN and BETWEEN
| You want… | Use |
|---|---|
| One of a few exact values | IN ('a', 'b', 'c') |
| Values that come from another table | IN (SELECT …) |
| A continuous range, ends included | BETWEEN low AND high |
| A date range on columns with times | >= start AND < next_day |
| A text pattern | LIKE (see SQL LIKE and Wildcards) |
That completes the main tools for reading data. The last two lessons switch to designing tables: SQL CREATE TABLE and then constraints and keys.