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

JOINS BRIEF

Joins allow you to combine related data from different tables into one result set.