WHERE and comparison operators

The idea

SELECT chooses columns. WHERE chooses rows. Together they turn "show me the whole table" into "show me the completed app orders from March", which is what real questions look like.

Your first filter

The products table has 15 products across five categories. If a customer asks "what coffee do you sell?", you don't want to hand them the whole list and let them search. WHERE adds a condition, and the database only returns the rows where that condition is true:

SELECT name, price
FROM products
WHERE category = 'Coffee';

 name                 | price
----------------------+--------
 Arabica Gayo 250g    |  95000
 Robusta Lampung 250g |  65000
 Toraja Kalosi 250g   | 110000
(3 rows)

• WHERE comes after FROM.
• Text values go in single quotes: 'Coffee'. Numbers don't need quotes: WHERE price = 95000.
• Text comparison is case-sensitive: 'coffee' matches nothing here, because the data says 'Coffee'.

Notice that category isn't in the SELECT list, yet you can filter on it. The database reads FROM first, then WHERE, then SELECT, so every column is available for filtering:

Comparison operators

They work on numbers, text (alphabetical order) and dates. Dates are written as text in 'YYYY-MM-DD' format:

SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2024-06-20';

 order_id | order_date | status
----------+------------+-----------
       36 | 2024-06-22 | shipped
       37 | 2024-06-24 | cancelled
       38 | 2024-06-25 | completed
       39 | 2024-06-25 | cancelled
       40 | 2024-06-29 | cancelled
(5 rows)

Combining conditions: AND, OR, NOT

• AND: both conditions must be true.
• OR: at least one must be true.
• NOT: flips a condition.

SELECT order_id, order_date, channel
FROM orders
WHERE status = 'completed'
  AND channel = 'app'
  AND order_date < '2024-03-01';

 order_id | order_date | channel
----------+------------+---------
        1 | 2024-01-03 | app
        3 | 2024-01-17 | app
        4 | 2024-01-18 | app
        5 | 2024-01-20 | app
        9 | 2024-02-14 | app
(5 rows)

The AND/OR trap

Here's a question from a manager: "Show coffee or kitchen products under Rp 100.000." A natural first attempt:

SELECT name, category, price
FROM products
WHERE category = 'Coffee' OR category = 'Kitchen' AND price < 100000;

 name                 | category | price
----------------------+----------+--------
 Arabica Gayo 250g    | Coffee   |  95000
 Robusta Lampung 250g | Coffee   |  65000
 Toraja Kalosi 250g   | Coffee   | 110000
 Ceramic Mug          | Kitchen  |  55000
(4 rows)

The Toraja coffee costs Rp 110.000, so why is it there? Because AND is evaluated before OR, just like multiplication before addition in maths. The database read the condition as:

category = 'Coffee'  OR  (category = 'Kitchen' AND price < 100000)

So every coffee product passes, whatever its price. Parentheses fix it:

SELECT name, category, price
FROM products
WHERE (category = 'Coffee' OR category = 'Kitchen')
  AND price < 100000;

 name                 | category | price
----------------------+----------+-------
 Arabica Gayo 250g    | Coffee   | 95000
 Robusta Lampung 250g | Coffee   | 65000
 Ceramic Mug          | Kitchen  | 55000
(3 rows)

IN: a shorter OR

When you compare one column against several values, IN is cleaner than a chain of ORs:

SELECT name, category, price
FROM products
WHERE category IN ('Coffee', 'Kitchen')
  AND price < 100000;

 name                 | category | price
----------------------+----------+-------
 Arabica Gayo 250g    | Coffee   | 95000
 Robusta Lampung 250g | Coffee   | 65000
 Ceramic Mug          | Kitchen  | 55000
(3 rows)

NOT IN keeps everything except the listed values: WHERE status NOT IN ('cancelled', 'pending').

BETWEEN: a range

Customers often shop within a budget: "something between 60 and 95 thousand". You could write that as price >= 60000 AND price <= 95000, but BETWEEN says it more naturally. BETWEEN low AND high includes both ends:

SELECT name, price
FROM products
WHERE price BETWEEN 60000 AND 95000;

 name                 | price
----------------------+-------
 Arabica Gayo 250g    | 95000
 Robusta Lampung 250g | 65000
 Desk Organizer       | 89000
 Scented Candle       | 75000
 Bamboo Plant         | 60000
(5 rows)

Both the Rp 60.000 plant and the Rp 95.000 coffee are included. For dates, order_date BETWEEN '2024-03-01' AND '2024-03-31' means "all of March".

A note on missing values

Dimas has no email, which is stored as NULL. You might try WHERE email = NULL, but it returns no rows at all, because NULL means "unknown" and "is unknown equal to unknown?" isn't true. The correct test is IS NULL:

SELECT name, email
FROM customers
WHERE email IS NULL;

 name          | email
---------------+-------
 Dimas Pratama | NULL
 Joko Susilo   | NULL
(2 rows)

The NULL module later in this track covers the details. For now: never use = NULL, use IS NULL or IS NOT NULL.

Why this matters for clean code

Most wrong numbers in reports aren't caused by crashes, they're caused by filters that run fine but mean something different from what the author intended. Parentheses around OR, IN instead of long OR chains, and one condition per line make your intent impossible to misread, for the database and for the next person.

Recap

• WHERE keeps rows where the condition is true; it runs before SELECT.
• Text and dates in single quotes; text matching is case-sensitive.
• Operators: =, <>, <, >, <=, >=.
• AND is evaluated before OR: always use parentheses when mixing them.
• IN (...) replaces a chain of ORs; BETWEEN a AND b includes both ends.
• Test for missing values with IS NULL, never = NULL.