This content originally appeared on DEV Community and was authored by John Wakaba
BETWEEN
It is case sensitive with string data types.
It is a crucial operator for filtering data within a specific range.
It signifies range based conditionsincluding both the lower and upper boundaries(inclusive of the two values)
SELECT * FROM meds WHERE price **BETWEEN** 1000 AND 4000;
In a date Scenario
SELECT * FROM patients WHERE appointment_date **BETWEEN** '2025-01-01 **AND** 2025-10-10';
- When making date queries date literals should be in ISO 8601 format YYY-MM-DD
When seeking to exclude values one can use the NOT operator.
IN
It is used to filter records by matching any value in a list
Simplifies complex queries, replacing multiple OR conditions with a single IN clause.
Checking against a list of values:
SELECT * FROM patient WHERE city **IN** ('Nairobi', 'Mombasa');
Using a Subquery
SELECT f_name, l_name FROM patient
WHERE
patient_id **IN** (SELECT patient_id from doctors WHERE diagnosis='Malaria');
- For large datasets use EXISTS/JOIN operators instead of IN operator.
LIKE OPERATOR
It is essential for pattern matching
LIKE is used to search for patterns in text data.
- % means any number of characters
Utilizes wild card search techniques and is commonly used with the WHERE clause for filtering records.
% Matching any sequence of characters
_ Matching any single characters
The above are two special wildcard characters
Patients with the fist name starting with K
SELECT * FROM patient WHERE f_name LIKE 'K%';
Patients with the fist name containing bert
SELECT * FROM patient WHERE f_name LIKE ''%bert%';
Patients with the email ending with gmail.com
SELECT * FROM patients WHERE email LIKE '%gmail.com';
Using the underscore
Patients where the first name begins with any single character and is followed by string ‘oma’ and ends with any number of characters
SELECT * FROM patient WHERE f_name LIKE '_oma%';
NOT LIKE
Used whenever we want to exclude records that match a certain pattern.
Best fit for when filtering out data that does not fit a certain criteria.
WHERE first_name NOT LIKE ‘ken%’
ILIKE
Used when performing case insensitive pattern matching
WHERE first_name ILIKE ‘ken%’
- It matches any string starting with ‘Ken’, ‘ken’, ‘KEN’ ignoring the others
This content originally appeared on DEV Community and was authored by John Wakaba