Filtering Data in SQL: A Beginner’s Guide to WHERE, AND, LIKE and Friends

When you first start writing SQL, pulling data from a database feels like opening a firehose. A basic SELECT * FROM patients; returns every single row in your table. But in real-world data analysis, you rarely want everything at once. You need to target specific insights—such as finding uninsured patients, tracking down records with partial email strings, or filtering by age ranges.

The secret to narrowing down your data lies in the WHERE clause and its rich toolkit of operators. In this guide, we will break down how to use comparison, logical, range, list, NULL, and pattern-matching operators using a practical healthcare dataset.

1. Comparison Operators (=, >, <)
At the core of data filtering are basic comparison operators. They allow you to test if values match, exceed, or fall below specific thresholds.

For example, if you want to pull all records for male patients, you use the standard equals operator (=):
SELECT *
FROM patients
WHERE gender = 'Male';
If you are working with numerical columns—such as filtering out patients with high medical bills exceeding a specific threshold—you can use greater-than (>) or less-than (<) operators:
SELECT *
FROM patients
WHERE bill_amount > 10000;

2. Range and List Operators (BETWEEN and IN)
When you need to check values across a continuous range or against multiple discrete options, SQL provides shorthand operators that keep your queries clean.

Checking Ranges with BETWEEN
Instead of writing compound inequalities with AND, the BETWEEN operator lets you specify an inclusive range. For instance, to find patients aged 25 to 45:
SELECT *
FROM patients
WHERE age BETWEEN 25 AND 45;
__Checking Lists with IN
When checking a column against multiple possible text values (like matching several counties), the IN operator is much cleaner than writing multiple OR statements:
SELECT *
FROM patients
WHERE county IN ('Nairobi', 'Mombasa', 'Kisumu');

1. Handling Missing Data with IS NULL In databases, missing or unknown information is represented by NULL. A common beginner pitfall is trying to find missing values using = NULL. Because NULL represents the absence of a value rather than a value itself, you must use the IS NULL operator.

To list patients who do not have an active insurance policy on file:
SELECT *
FROM patients
WHERE insurance IS NULL;

1. Pattern Matching with LIKE and Wildcards When you need to search for text patterns rather than exact words, the LIKE operator comes into play alongside wildcards:

% matches zero or more characters.

_ matches a single character.

For example, to pull all patients whose email addresses belong to the Gmail domain:
SELECT *
FROM patients
WHERE email LIKE '%gmail.com';

Common Mistakes to Avoid
As you build out complex queries combining multiple operators, keep these three classic errors in mind:

Using = NULL instead of IS NULL: Writing WHERE insurance = NULL will always return zero rows because nothing equals an unknown value. Always use IS NULL or IS NOT NULL.

1. Case Sensitivity with LIKE: Standard LIKE in PostgreSQL is strictly case-sensitive. Searching for LIKE 'k%' will miss uppercase entries like 'Kiprono'. If you need to ignore casing, use ILIKE instead.
2. Missing Brackets with NOT IN: When using list operators like IN or NOT IN, you must always enclose your target values in parentheses (e.g., county NOT IN ('Nairobi')), otherwise, PostgreSQL will throw a syntax error.

Conclusion
Mastering these filtering operators transforms raw tables into targeted datasets, giving you precise control over your queries. Try experimenting with combinations of BETWEEN, IN, and LIKE in your own practice database to build confidence for your data analytics journey!