SQL Operators Made Simple: Learn with Practical Examples

Introduction

SQL (Structured Query Language) is used to retrieve, filter, and manage data stored in databases. When working with large datasets, it is important to find specific information instead of viewing every record. SQL operators help us compare values, combine conditions, perform calculations, and search for patterns.

In this article, I will summarise what I learned about SQL operators this week using practical examples from a hospital database.

1. Comparison Operators

Comparison operators compare values and filter records based on conditions. Common operators include =, <>, >, <, >=, and <=.

For example, I used the greater-than operator to identify patients whose hospital bills exceeded 100,000.

SELECT first_name, department, bill_amount
FROM hospital.patients
WHERE bill_amount > 100000;

This query returns patients whose bills are greater than 100,000. The <> operator can also be used to find values that are not equal to a specified value.

2. Logical Operators

Logical operators combine conditions. AND requires all conditions to be true, OR requires at least one condition to be true, and NOT reverses a condition.

For example, this query finds female patients admitted to the Maternity department.

SELECT *
FROM hospital.patients
WHERE gender = 'Female'
  AND department = 'Maternity';

Both conditions must be true for a patient to appear in the results. Parentheses can help clarify queries that combine AND and OR.

3. Range and List Operators

The BETWEEN operator checks whether a value falls within a range, including both endpoints. The IN operator checks whether a value matches any item in a list.

To find patients whose bills fall between 10,000 and 50,000:

SELECT first_name, bill_amount
FROM hospital.patients
WHERE bill_amount BETWEEN 10000 AND 50000;

To find patients from Nairobi or Mombasa:

SELECT *
FROM hospital.patients
WHERE county IN ('Nairobi', 'Mombasa');

Using IN makes the query easier to read than writing multiple OR conditions.

4. Mathematical and Arithmetic Operators

Arithmetic operators perform calculations on numeric values.

Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Remainder

For example, we can calculate a 10% increase in a patient's bill for demonstration purposes:

SELECT first_name,
       bill_amount,
       bill_amount * 1.10 AS adjusted_bill
FROM hospital.patients;

The calculated value appears under adjusted_bill. This query does not change the original bills stored in the table.

5. Assignment Operators

Assignment involves setting or updating a value. In PostgreSQL, = is used to assign a value in an UPDATE statement.

For example:

UPDATE hospital.patients
SET bill_amount = 50000
WHERE patient_id = 1;

This sets the bill for the patient with ID 1 to 50,000. The WHERE clause is important because leaving it out could update every patient's bill.

To increase an existing bill by 1,000, we can write:

UPDATE hospital.patients
SET bill_amount = bill_amount + 1000
WHERE patient_id = 1;

PostgreSQL does not use += as a standard SQL assignment operator.

6. NULL Values and Identity-Related Comparisons

NULL represents a missing or unknown value. I learned to use IS NULL to find missing information and IS NOT NULL to find information that is present.

For example, this query finds patients without recorded email addresses:

SELECT first_name, last_name
FROM hospital.patients
WHERE email IS NULL;

Using email = NULL does not work as expected because SQL uses special rules for NULL comparisons.

In Python, is and is not are identity operators. They are not used in the same way in SQL. PostgreSQL provides IS DISTINCT FROM for comparisons that handle NULL values explicitly.

7. Pattern Matching with LIKE

The LIKE operator searches for text patterns. The % wildcard matches zero or more characters, while _ matches exactly one character.

For example, to find patients whose first names begin with K:

SELECT first_name, last_name
FROM hospital.patients
WHERE first_name LIKE 'K%';

In PostgreSQL, LIKE is case-sensitive. The ILIKE operator can be used for case-insensitive matching.

8. Common Mistakes to Avoid

Some common mistakes when using SQL operators include:

• Using = NULL instead of IS NULL.
• Forgetting that BETWEEN includes both endpoints.
• Combining AND and OR without parentheses when the intended logic is unclear.
• Forgetting that LIKE is case-sensitive in PostgreSQL.
• Running an UPDATE without checking its WHERE condition.

SQL also includes commands such as CREATE TABLE, ALTER TABLE, DELETE, TRUNCATE, and DROP TABLE. These are database commands rather than operator categories. DELETE removes selected rows, TRUNCATE removes all rows while retaining the table structure, and DROP TABLE removes the table itself.

Conclusion

Learning SQL operators has helped me understand how to filter records, combine conditions, perform calculations, handle missing values, and search for patterns. Practising these operators using a hospital database has strengthened my understanding of how SQL retrieves and manages information. These skills provide a foundation for writing more accurate and efficient queries.