Lesson 4 of 26 · see the practice database

AND, OR, NOT, IN and BETWEEN

One condition is often not enough. You want the stationery that costs under 100 rupees, or the customers from Delhi or Mumbai. This lesson joins conditions together and then shows two shortcuts, IN and BETWEEN.

AND: both must be true

SELECT name, category, price
FROM products
WHERE category = 'Stationery' AND price < 100;

A row is returned only if it passes both tests. Each AND you add makes the result smaller or leaves it the same.

OR: at least one must be true

SELECT name, city
FROM customers
WHERE city = 'Delhi' OR city = 'Mumbai';

A row is returned if it passes either test. Each OR you add makes the result larger or leaves it the same.

Each side of an OR must be a complete condition. WHERE city = 'Delhi' OR 'Mumbai' is wrong, though it may not give an error. Write the column name again on each side.

NOT: the opposite

NOT turns a condition around.

SELECT name, category
FROM products
WHERE NOT category = 'Grocery';

This is the same as category <> 'Grocery'. NOT is most useful together with IN, BETWEEN and LIKE, as you will see below.

Mixing AND and OR: use brackets

When a condition has both AND and OR, the database does the AND parts first, the same way multiplication comes before addition in arithmetic. That can give a result you did not intend.

Suppose you want products in Grocery or Home that cost more than 400 rupees.

SELECT name, category, price
FROM products
WHERE category = 'Grocery' OR category = 'Home' AND price > 400;

Look at the result. Cheap groceries are in it. The database read the condition as "Grocery, or (Home and over 400)". Brackets fix it.

SELECT name, category, price
FROM products
WHERE (category = 'Grocery' OR category = 'Home') AND price > 400;

Make it a rule: whenever AND and OR appear together, add brackets, even where they are not strictly needed. The reader, and you in a month, will not have to think about it.

IN: one of a list

A chain of OR tests on the same column is long to write. IN does the same with a list.

SELECT name, city
FROM customers
WHERE city IN ('Delhi', 'Mumbai', 'Pune');

It works with numbers too.

SELECT id, customer_id, status
FROM orders
WHERE customer_id IN (1, 2);

NOT IN gives the rows whose value is not in the list.

SELECT name, department
FROM employees
WHERE department NOT IN ('Sales', 'Support');

BETWEEN: inside a range

BETWEEN tests whether a value lies in a range. Both ends are included.

SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 300;

This is the same as price >= 100 AND price <= 300. The smaller value must come first. BETWEEN 300 AND 100 returns nothing.

It works well with dates. These are the orders placed in the first three months of 2026.

SELECT id, order_date
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

A warning for later: if a column stores a time of day as well as a date, BETWEEN '2026-01-01' AND '2026-03-31' misses almost all of the last day, because 31 March at 10 in the morning is later than 31 March at midnight. For such columns, write >= '2026-01-01' AND < '2026-04-01'. Our practice database stores plain dates, so BETWEEN is safe here.

NOT BETWEEN gives the rows outside the range.

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 35000 AND 60000;

Putting it together

Here is a realistic question: which delivered or shipped orders were placed from April to June 2026?

SELECT id, order_date, status
FROM orders
WHERE status IN ('delivered', 'shipped')
  AND order_date BETWEEN '2026-04-01' AND '2026-06-30';

When a condition gets long, put each part on its own line, starting with AND or OR. It reads like a checklist.

Try it yourself

SELECT name, category, price FROM products WHERE category = 'Electronics' AND price < 450;

SELECT name, state FROM customers WHERE state IN ('Punjab', 'Kerala');

SELECT name, stock FROM products WHERE stock BETWEEN 20 AND 50;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.