WHERE: Filtering Rows

WHERE filters rows: it tells PostgreSQL which records are allowed into the result grid. If SELECT chooses the columns you see, WHERE chooses the rows that survive.

Think of WHERE as the check at the cafe counter. Every menu row walks up, the condition asks one yes-or-no question, and only the yes rows reach your answer.

Filtering with comparisons

A condition is a true-or-false test that SQL runs for each row. A comparison operator compares two values: = means equal, <> means not equal, and <, >, <=, >= compare size.

Start with the most common filter: one category.

SELECT name, category, price
FROM menu_items
WHERE category = 'coffee';

    name    | category | price
------------+----------+-------
 Espresso   | coffee   |  2.50
 Cappuccino | coffee   |  3.80
 Flat White | coffee   |  4.00
 Cold Brew  | coffee   |  4.50
(4 rows)

Read it slowly: from menu_items, keep rows where category equals the text value 'coffee', then show name, category, and price. The row-count footer says four rows survived.

For this lesson, the condition is true or false. Section 3 adds the weird third case, when a fact is unknown.

The quote and equals traps

SQL equality uses one =, not ==. If you bring the JavaScript or Python spelling with you, PostgreSQL does not guess what you meant:

SELECT name
FROM menu_items
WHERE category == 'coffee';

ERROR:  operator does not exist: text == unknown
LINE 3: WHERE category == 'coffee'
                       ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Text values use single quotes. Double quotes mean "a column or table name," so "coffee" asks PostgreSQL for a column named coffee:

SELECT name
FROM menu_items
WHERE category = "coffee";

ERROR:  column "coffee" does not exist
LINE 3: WHERE category = "coffee"
                         ^

The same rule holds for every text filter you write: 'tea', 'pastry', 'Cold Brew', and so on. Numbers do not take quotes: price >= 4.00 compares a numeric value.

Combining conditions

A logical operator combines true-or-false conditions. AND keeps a row only when both sides pass. OR keeps a row when either side passes. NOT flips a condition.

Suppose the cafe wants unavailable coffee or tea items. The first try looks reasonable:

SELECT name, category, in_stock
FROM menu_items
WHERE category = 'coffee'
  OR category = 'tea'
  AND NOT in_stock;

    name    | category | in_stock
------------+----------+----------
 Espresso   | coffee   | t
 Cappuccino | coffee   | t
 Flat White | coffee   | t
 Cold Brew  | coffee   | f
 Sencha     | tea      | f
(5 rows)

That grid is wrong for the story. Espresso, Cappuccino, and Flat White are coffee, but they are in stock. The query quietly kept them.

Use parentheses when the human sentence has grouping:

SELECT name, category, in_stock
FROM menu_items
WHERE (category = 'coffee' OR category = 'tea')
  AND NOT in_stock;

   name    | category | in_stock
-----------+----------+----------
 Cold Brew | coffee   | f
 Sencha    | tea      | f
(2 rows)

Now the condition says: first decide whether the item is coffee or tea; then require it to be out of stock. Parentheses are not decoration. They are part of the meaning.

BETWEEN, IN, and LIKE

IN checks whether a value appears in a list. It is the clean version of category = 'coffee' OR category = 'tea'. BETWEEN checks an inclusive range, so BETWEEN 3.00 AND 4.00 includes both 3.00 and 4.00.

SELECT name, category, price
FROM menu_items
WHERE category IN ('coffee', 'tea')
  AND price BETWEEN 3.00 AND 4.00;

    name    | category | price
------------+----------+-------
 Cappuccino | coffee   |  3.80
 Flat White | coffee   |  4.00
 Earl Grey  | tea      |  3.00
 Sencha     | tea      |  3.40
(4 rows)

LIKE matches text patterns. % means "any number of characters." _ means "exactly one character." ILIKE is PostgreSQL's case-blind accent: it works like LIKE, but ignores letter case.

SELECT name
FROM menu_items
WHERE name LIKE 'C%'
  OR name LIKE 'Earl Gr_y'
  OR name ILIKE '%bread%';

     name
---------------
 Cappuccino
 Cold Brew
 Earl Grey
 Croissant
 Cinnamon Roll
 Banana Bread
(6 rows)

C% finds names that start with C. Earl Gr_y finds Earl Grey because _ fills exactly one character. %bread% finds Banana Bread even though the pattern is lowercase, because ILIKE ignores case.

You can now ask for the rows you mean instead of reading the whole table. Next, you will learn that a filtered set still has no promised order; ORDER BY and LIMIT give you control over which rows appear first and how many you take.