Introduction
I learned how to use SQL operators to filter and retrieve specific records from a database. I practised these concepts using PostgreSQL and a hospital database containing patient information such as names, ages, counties, insurance providers, departments, bills, and admission dates.
SQL operators are important because they help us retrieve meaningful information without having to examine every record manually. For example, a hospital might want to find female patients admitted to the maternity department, patients with bills above KSh 50,000, or patients whose email addresses are missing.
In this article, I will share what I learned about comparison operators, logical operators, range and list operators, NULL handling, and pattern matching in SQL.
1. Comparison Operators
Comparison operators allow us to compare values in a database. They include:
= — equal to
<> — not equal to
— greater than
< — less than
= — greater than or equal to
<= — less than or equal to
For example, I can retrieve patients whose bills exceed KSh 150,000.
SELECT *
FROM patients
WHERE bill_amount > 150000;
This query returns patients whose bills are greater than 150,000. A bill of exactly 150,000 would not be included because the operator is > rather than >=.
I also learned that comparison operators work with different data types. Numeric comparisons work with numbers, while date comparisons can identify records within specific periods.
For example:
SELECT *
FROM patients
WHERE admission_date >= '2026-01-01'
AND admission_date <= '2026-03-31';
This retrieves patients admitted during the first quarter of 2026.
2. Logical Operators: AND, OR, and NOT
Logical operators combine or reverse conditions.
AND requires all specified conditions to be true. For example, to find female patients in the maternity department:
SELECT *
FROM patients
WHERE gender = 'Female'
AND department = 'Maternity';
Both conditions must be satisfied for a patient to appear in the results.
OR returns records when at least one condition is true. For example:
SELECT *
FROM patients
WHERE county = 'Kisumu'
OR county = 'Nairobi';
This retrieves patients from either Kisumu or Nairobi.
NOT reverses a condition. For example:
SELECT *
FROM patients
WHERE department NOT IN
('Cardiology', 'Emergency', 'Orthopedics');
This excludes patients in the listed departments.
These operators are useful when business questions require multiple conditions to be evaluated together.
3. Range and List Operators: BETWEEN and IN
I learned that BETWEEN checks whether a value falls within a specified range. Its boundaries are inclusive, meaning both the minimum and maximum values are included.
For example:
SELECT *
FROM patients
WHERE bill_amount BETWEEN 10000 AND 50000;
This returns patients whose bills range from 10,000 to 50,000, including patients with bills of exactly 10,000 or 50,000.
The IN operator checks whether a value matches any item in a specified list. It can make queries shorter and easier to read.
SELECT *
FROM patients
WHERE insurance IN ('SHA', 'AAR', 'Britam');
This retrieves patients insured by SHA, AAR, or Britam.
Instead of writing several conditions joined by OR, I can use IN when checking one column against multiple possible values.
4. Working with NULL Values
Another important lesson was understanding NULL. In SQL, NULL represents a missing or unknown value. It is different from zero, an empty string, or the text 'NULL'.
To find patients whose email addresses are missing, I use:
SELECT *
FROM patients
WHERE email IS NULL;
To find patients who have an email address, I use:
SELECT first_name, last_name, insurance
FROM patients
WHERE email IS NOT NULL;
A common mistake is writing email = NULL. This does not correctly test for missing values because comparisons with NULL generally produce an unknown result.
The correct operators are IS NULL and IS NOT NULL.
This knowledge is particularly useful when cleaning datasets and identifying incomplete records before analysis.
5. Pattern Matching with LIKE and ILIKE
Pattern matching helps retrieve records based on text patterns.
The LIKE operator is case-sensitive in PostgreSQL. Two useful wildcards are:
% represents zero or more characters.
_ represents exactly one character.
For example, to find patients whose first names begin with K:
SELECT *
FROM patients
WHERE first_name LIKE 'K%';
To find patients whose email addresses end in gmail.com:
SELECT *
FROM patients
WHERE email LIKE '%gmail.com';
I also learned that ILIKE performs case-insensitive pattern matching in PostgreSQL.
SELECT *
FROM patients
WHERE first_name ILIKE 'k%';
Unlike LIKE 'k%', this query can match names beginning with either lowercase or uppercase K.
These operators are helpful when searching customer names, email addresses, product descriptions, and other text fields.
6. Common Mistakes to Avoid
During my practice, I identified several mistakes beginners should watch out for.
Using = NULL: Use IS NULL instead.
Forgetting that LIKE is case-sensitive: In PostgreSQL, LIKE 'k%' will not match Kevin. Use ILIKE 'k%' when case-insensitive matching is required.
Misunderstanding BETWEEN: Both boundaries are included. Use > and < when the boundaries should be excluded.
Misusing AND and OR: Logical operators can produce unexpected results when conditions are not grouped correctly. Parentheses make the intended logic explicit.
For example, if I want patients from Kisumu or Nairobi who have bills above 50,000, I should write:
SELECT *
FROM patients
WHERE (county = 'Kisumu' OR county = 'Nairobi')
AND bill_amount > 50000;
The parentheses ensure that the county condition is evaluated together before applying the bill requirement.
Conclusion
SQL practice helped me understand how operators make database queries more precise and useful. I learned how to compare values, combine conditions, filter ranges and lists, identify missing data, and search for text patterns using PostgreSQL.
These skills are useful in data analytics, reporting, database administration, and application development. I plan to keep practicing with larger datasets so that I can write more efficient queries and answer real-world questions with confidence.
Top comments (1)
The parentheses example for AND and OR is the part beginners should keep. It is easy to write a query that runs fine but returns the wrong set. One concrete habit that helps: build the query one condition at a time and check the row count after each step, so a wrong grouping shows up right away instead of hiding in a long WHERE clause.