E M

Posted on Oct 26

SQL for Beginners

#datascience #sql #database

We'll walk through the operators you actually need as a beginner—comparison, logical, range and list, NULL handling, and pattern matching—all in PostgreSQL, with queries I actually ran against a patients table.

Comparison Operators

Comparison operators are the ones you'll use every single day. They return a boolean for every row, and WHERE keeps only the rows where the result is true. The core set is =, <> (or !=), <, >, <=, and >=.

Here's a query for a patients table:

SELECT patient_id, patient_name, bill_amount
FROM patients
WHERE bill_amount > 10000;

Simple enough. The trouble starts when you combine conditions.

Logical Operators: AND & OR

AND requires both sides to be true. OR requires at least one. NOT negates a condition. But they don't have equal priority—AND is evaluated before OR.

I wrote this query to find male patients over 40, or anyone with a huge bill:

SELECT *
FROM patients
WHERE gender = 'Male' AND age > 40
   OR bill_amount > 150000;

It works but only by luck. The database parsed it as (gender = 'Male' AND age > 40) OR (bill_amount > 150000), which is what I intended. But if I'd added another AND later, the grouping could have shifted silently.

The fix is boring and essential: use parentheses.

SELECT *
FROM patients
WHERE (gender = 'Male' AND age > 40)
   OR (bill_amount > 150000);

Now the logic is unmistakable to the next person who reads it—including future you.

Range and List Filtering: BETWEEN and IN

BETWEEN is shorthand for a range check, and in PostgreSQL it's inclusive on both ends. a BETWEEN x AND y is exactly a >= x AND a <= y.

I used it to find patients with bills in a middle band:

SELECT *
FROM patients
WHERE bill_amount BETWEEN 10000 AND 50000;

If a patient's bill is exactly 50000, they appear in the results. Same for exactly 10000. This tripped me up at first—I assumed BETWEEN might be exclusive on the upper end. It isn't. I verified with a row where bill_amount = 50000, and it came back.

IN is the cleaner way to check against a list:

SELECT *
FROM patients
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

Logically, IN (a, b, c) is the same as = a OR = b OR = c. For short lists, readability is the main win. For very long lists, consider VALUES + JOIN instead.

NULL

NULL means "unknown," not "empty." So column = NULL doesn't return true—it returns NULL (which WHERE treats as not-true), and you get zero rows.

-- WRONG: returns nothing
SELECT *
FROM patients
WHERE bill_amount = NULL;

-- RIGHT: use IS NULL
SELECT *
FROM patients
WHERE bill_amount IS NULL;

If you need to treat NULL as a comparable value (for example, checking whether two columns differ even when one is NULL), PostgreSQL offers IS DISTINCT FROM and IS NOT DISTINCT FROM. For everyday filtering, IS NULL and IS NOT NULL are all you need.

LIKE vs. ILIKE: Case Sensitivity Matters

LIKE does pattern matching with two wildcards: % matches any number of characters, _ matches exactly one.

SELECT *
FROM patients
WHERE email LIKE '%@gmail.com';

This finds addresses ending in @gmail.com. It will not match @Gmail.com or @GMAIL.COM—LIKE is case-sensitive.

ILIKE is the case-insensitive version, and it's a PostgreSQL extension:

SELECT *
FROM patients
WHERE email ILIKE '%@gmail.com';

Now @gmail.com, @Gmail.com, and @GMAIL.COM all match.

A Real Business Query

A clinic wants to text young adult men who don't live in Nairobi, have no insurance, and have a recorded phone number:

-- Which male patients aged 18-35, NOT living in Nairobi,
-- with no insurance and a recorded phone number, should be contacted?
SELECT *
FROM patients
WHERE gender = 'Male'
  AND age BETWEEN 18 AND 35
  AND city <> 'Nairobi'
  AND insurance = 'No'
  AND phone IS NOT NULL
ORDER BY age, city;

Each line uses a different operator, and together they express a rule that would be painful to describe in plain English.

Operator Precedence

When an expression mixes operators, SQL follows a fixed evaluation order. Simplified, from highest to lowest: arithmetic → comparison → NOT → AND → OR. When unsure, just add parentheses—it's the safest and most readable approach.

If you take one thing from this post, make it this: never write = NULL.

It doesn't show errors or warnings.

-- This will never match anything
WHERE bill_amount = NULL;

-- Always use this instead
WHERE bill_amount IS NULL;

The same principle applies to <> NULL and NOT IN with NULL in the list—NULL propagates through comparisons and can surprise you. When in doubt, handle NULL explicitly.

Operators are the grammar of SQL filtering. The small rules that aren't obvious from the syntax are what trip beginners up: AND before OR, BETWEEN is inclusive, NULL isn't equal to anything, and LIKE cares about case.

Don't try to memorize every operator at once. Start with =, >, <, AND, and OR, then move on to IN, BETWEEN, and LIKE. The rest can wait until you actually need them.

DEV Community