Filter Rows: AND, OR, IN, BETWEEN and LIKE

A single WHERE price > 500 answers simple questions. Real questions have more parts: customers in Nairobi who joined after May, or products that are cheap or in the Home category. In this lesson you combine conditions and learn the shortcuts that make long filters short and clear.

AND: both must be true

AND keeps a row only when both conditions are true.

SELECT name, city, joined
FROM customers
WHERE city = 'Nairobi' AND joined >= '2024-05-01';

name         | city    | joined
Grace Mwangi | Nairobi | 2024-05-09

Two customers live in Nairobi, Farid and Grace, but only Grace joined in May or later, so only Grace remains. Each extra AND makes the result smaller or the same, never bigger.

OR: at least one must be true

OR keeps a row when either condition is true.

SELECT name, category, price
FROM products
WHERE category = 'Bags' OR price < 100;

name         | category   | price
Pen pack     | Stationery | 80
Backpack     | Bags       | 1400
Sticky notes | Stationery | 60

Each extra OR makes the result bigger or the same. A good way to remember: AND narrows, OR widens.

Brackets: the trap in mixing AND and OR

When you mix AND and OR in one WHERE, SQL does AND first, like multiplication before addition in maths. This often gives a different answer than you meant. Use brackets to say exactly what you want.

-- What you may mean: Office or Bags products that cost under 1500
SELECT name, category, price
FROM products
WHERE (category = 'Office' OR category = 'Bags') AND price < 1500;

name     | category | price
Backpack | Bags     | 1400
Mouse    | Office   | 700

Without the brackets, SQL would read category = 'Office' OR (category = 'Bags' AND price < 1500). Then the Laptop stand at 1800 would sneak into the result, because every Office product passes the first test. Whenever you use both words together, add brackets even when you think you do not need them. It costs nothing and protects you from wrong reports.

IN: a short way to write many ORs

Writing category = 'Home' OR category = 'Bags' OR category = 'Office' is long. IN does the same with a list.

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

This returns Desk lamp, Backpack and Water bottle. Add NOT in front to get everything else: category NOT IN ('Home', 'Bags'). A warning for later: if the list could contain NULL, NOT IN can behave strangely. You will meet this in the subqueries lesson.

BETWEEN: a range, with both ends included

SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 700
ORDER BY price DESC;

name         | price
Mouse        | 700
Desk lamp    | 650
Water bottle | 300
Notebook     | 120

BETWEEN 100 AND 700 is the same as price >= 100 AND price <= 700. Both ends are included, which is why the Mouse at exactly 700 appears. It also works with dates: joined BETWEEN '2024-02-01' AND '2024-03-31'.

LIKE: match patterns in text

When you do not know the exact text, LIKE searches by pattern. Two special signs are used. The percent sign % stands for any number of characters, including none. The underscore _ stands for exactly one character.

-- names that start with A
SELECT name FROM customers WHERE name LIKE 'A%';

-- names that end with 'Rao'
SELECT name FROM customers WHERE name LIKE '%Rao';

-- names with a letter 'k' anywhere
SELECT name FROM customers WHERE name LIKE '%k%';

-- a three-letter product code starting with P, like P01
SELECT name FROM products WHERE name LIKE 'P__';

In SQLite, LIKE ignores upper and lower case for ordinary English letters. In some other engines it does not, so check your database if case matters. Be careful with % at the start of a pattern, such as '%k%'. The database has to look at every row, because it cannot jump straight to a match. On a small table you will not notice. On a table with millions of rows, it can be slow.

Putting it all together

Here is a realistic request: “Show orders that are either shipped or pending, placed in July or August 2024.” You can build it from the parts.

SELECT id, customer_id, order_date, status
FROM orders
WHERE status IN ('shipped', 'pending')
  AND order_date BETWEEN '2024-07-01' AND '2024-08-31'
ORDER BY order_date;

id | customer_id | order_date | status
6  | 5           | 2024-07-09 | shipped
9  | 1           | 2024-08-02 | shipped
10 | 7           | 2024-08-05 | pending

Notice how the query is laid out: one condition per line, with AND at the start of the line. This is not required, but it makes long conditions easy to read and easy to fix. People who read your SQL later, including you in a month, will be thankful.

Common mistakes

• Mixing AND and OR without brackets and getting a bigger or smaller list than expected.
• Writing WHERE price BETWEEN 700 AND 100. The smaller number must come first, or nothing is returned.
• Using = with %. name = 'A%' looks for the exact text A%. Use LIKE.
• Forgetting that NULL rows never match a comparison. A row with a missing city will not appear in city <> 'Nairobi'. Add OR city IS NULL if you want it.

Your task

1. Find products in the Stationery category that cost less than 100.
2. Find customers in Nairobi or Chennai (write it once with OR and once with IN).
3. Find orders that are not delivered (status <> 'delivered') and were placed after '2024-07-01'.
4. Find product names that contain the word note.

How to check it worked

• The first query returns Pen pack and Sticky notes.
• Both versions of the Nairobi or Chennai query return the same three customers: Divya, Farid and Grace.
• The product search for %note% returns Notebook and Sticky notes.