Filtering Data in SQL: A Beginner's Guide to WHERE, IN, BETWEEN, NULL, and LIKE

Hi everyone! I am learning SQL, and this week we studied operators. Operators are the words and symbols we write after WHERE to tell SQL which rows we want. I practised on a practice hospital table called patients in PostgreSQL. It has columns like county, department, age, bill_amount, insurance and `email. In this post, I explain what I learned, using queries I wrote myself.

1. Comparison operators

Comparison operators compare a column with a value. They are =, != (or <>), >, <, >= and <=.

sql
SELECT first_name, last_name, department, bill_amount
FROM patients
WHERE bill_amount > 100000;


This shows only the patients whose bill is more than 100,000. Two small things I noticed. Text must be in single quotes, like county = 'Kisumu', but numbers must not be. For dates, the YYYY-MM-DD format, like '2026-04-01', is the safest.

2. Logical operators: AND, OR and NOT

AND means both conditions must be true. OR means at least one must be true.

sql
SELECT *
FROM patients
WHERE gender = 'Female'
AND department = 'Maternity';


This gives me only female patients in the Maternity department. A row must pass both tests to appear.

For OR, I first wrote county = 'Nairobi' OR county = 'Mombasa'. It works, but I prefer IN because it groups the values clearly. I explain IN in the next section.

One more thing. SQL does AND before OR. When I use both in one query, I put brackets around the part that belongs together, so the meaning is clear to anyone reading it.

sql
SELECT *
FROM patients
WHERE (gender = 'Male' AND age > 40) OR bill_amount > 150000;


3. Range and list operators: BETWEEN and IN

BETWEEN selects values inside a range, and it includes both ends. I wanted to be sure, so I tested it. One patient has a bill of exactly 45,000. When I made 45,000 the upper limit, that patient was still in the result. This proved that the last number is included.

sql
SELECT first_name, bill_amount
FROM patients
WHERE bill_amount BETWEEN 10000 AND 45000;


IN checks a column against a list of values. It is shorter than writing many OR conditions. Adding NOT gives the opposite.

sql
SELECT *
FROM patients
WHERE department NOT IN ('Cardiology', 'Orthopedics', 'Emergency');


This gives me every patient who is not in those three departments.

4. NULL: a missing value

NULL does not mean zero, and it does not mean an empty space. It means the value is missing or unknown. To find these rows, we use IS NULL. To find rows that do have a value, we use IS NOT NULL.

sql
SELECT *
FROM patients
WHERE email IS NULL;


This lists the patients who have no email on file. In a real hospital, a list like this would show the staff which records are incomplete.

5. LIKE: searching for patterns

LIKE searches for text that follows a pattern. It has two special symbols:

• % means any number of characters, even none
• _ means exactly one character

sql
SELECT *
FROM patients
WHERE first_name LIKE 'K%';


This finds every first name that starts with K. I also tried '%gmail%' on the email column to find Gmail users, and '_i%' to find names with i as the second letter.

Common mistakes

Mistake 1: using = NULL. I wrote this on purpose to see what happens.

sql
SELECT *
FROM patients
WHERE email = NULL;


It returns nothing, even though some emails are missing. NULL means unknown, so nothing can be equal to it. The correct way is IS NULL.

**Mistake 2: forgetting that LIKE is case-sensitive.
**In PostgreSQL, LIKE sees K and k as two different letters. When I searched for 'k%' in lowercase, I got no rows because the names start with a capital K. The fix is ILIKE, which "ignores k and K”

sql
SELECT *
FROM patients
WHERE first_name ILIKE 'k%';


Mistake 3: mixing AND with OR without brackets. Because AND runs first, a query can return rows I did not mean to include. Brackets make my meaning clear.

What I learned

Filtering is easy once you know the operators, but small things change the answer: quotes, NULL, capital letters, and brackets. From now on, I will check my results every time, not only check that the query ran without an error.

I am still learning, so if you have tips for a beginner, I would love to hear them. Thank you for reading!