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

Introduction

As they say SQL is the backbone or foundation of data not really sure which one it is but, all that to say SQL forms a crucial part in data and data projects. At the moment there is tans and tans of data being produced all the time, increasing the scope of work for any data professional. Hence the need for filtering and that why we need the WHERE clause in SQL alongside its partners that make work easier and bearable.

The WHERE clause is used to filter rows based on one or more conditions. It ensures that queries return or modify only the required records.

Filters data based on specified conditions.
Used with SELECT, UPDATE and DELETE statements.
Works with comparison and logical operators.

Filtering using WHERE

Comparison Operators

They come in handy when comparing two values and filters the result set based on a specific condition as well as help check for equality, range, or inequality
Make SQL queries powerful and precise
They include: =, >, <, >=, <=, <>

Syntax

SELECT * FROM TABLE_NAME 
WHERE ATTRIBUTE CONDITION_OPERATOR GIVEN_VALUE;

Examples from our fictional hospital dataset:

Listing all patients from Kisumu county. ( = , equal to )
select * from patients where county ='Kisumu';

ListIng all patients who are not in the General department. ( <> or != , note equal to)
select * from patients where department <> 'General';

Showing the first name, department and bill amount of patients whose bill is greater than 100,000.
select first_name, department, bill_amount from patients where bill_amount > 100000;

Showing patients aged 18 or younger. ( <= , less than)
select * from patients where age <= 18;

Lising patients admitted on or after 1 April 2026. ( >= on a date)
select * from patients where admission_date >= '2026-04-01';

All these examples demonstrate comparison of one variable from another in the same column.

Logical operators AND, IN and BETWEEN

Used to test conditions in queries, returning results as TRUE, FALSE or UNKNOWN. They help in combining, negating and comparing conditions, enabling precise data retrieval and filtering. i.e IN,

AND Operator

AND combines multiple conditions in an SQL query and returns only the records that satisfy all the specified conditions.

Example: Listing female patients in the Maternity department. ( AND )
select * from patients where gender = 'Female' and department = 'Maternity';

Output:

NOT Operator

NOT is used to reverse the result of a condition, returning TRUE when the condition is FALSE. It is typically used to exclude records that match a specific condition, making it useful for filtering out unwanted data.

An example would be,
Listing patients who are not in Cardiology, Orthopedics or Emergency.

Query
select * from patients where department not in ( 'Cardiology');

Output

OR Operator

OR combines multiple conditions in a SQL query and returns TRUE if at least one of the conditions is satisfied. It is ideal for situations where you want to retrieve records that meet any of several possible conditions.

Example: Listing patients from Nairobi or Mombasa, written with OR .
select * from patients where county = 'Mombasa' or county ='Nairobi';

Output

Range/List

IN Operator

IN checks whether a value matches any value in a specified list, providing a concise alternative to multiple OR conditions.

Example: Listing patients covered by SHA, AAR or Britam.
select * from patients where insurance in('SHA', 'AAR', 'Britam');

Output:

BETWEEN OPERATOR

BETWEEN in SQL checks whether a value lies within a specified range, including both the lower and upper limits.

Example
Listing patients who are not aged between 18 and 60.
select * from patients where age not between 18 and 60;

Output

Query
Listing patients who are not in Cardiology, Orthopedics or Emergency.
select * from patients where department not in ( 'Cardiology');

Output

NULL

If a field in a table is optional, it is possible to insert or update a record without adding any value to this field. This way, the field will be saved with a NULL value.

A NULL value represents an unknown, missing, or inapplicable data in a database field. It is not a value itself, but a placeholder to indicate the absence of data.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Example: Listing patients with no email address.
select * from patients where email is null;

Output

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example Listing the first name, last name and insurance of patients who have insurance. (
select first_name, last_name, insurance from patients where insurance is not null;

Output

Conclusion

LIKE and ILIKE

PostgreSQL is a case-sensitive language in order to make it a case-insensitive language during pattern matching we make use of the ILIKE operator.

ILIKE operator works the same way as a LIKE operator but makes the language case-insensitive. Both the operators are used for pattern matching in PostgreSQL.

Example Listing patients whose first name starts with K.
select * from patients where first_name like 'K%';
Gives this Output

While
select * from patients where first_name like 'k%';
Returns zero rows as there are no rows where the first_name starts with lowercase k.

Instead we can use this query;
select * from patients where first_name ilike 'k%';
As it returns the same number of columns as the first query we tried.
select * from patients where first_name like 'K%';