WHERE: filtering rows

WHERE: the beam that reveals only the rows you need

Behind the terminal lies the vault: darkness thousands of rows deep.
Lighting all of it at once is pointless — data can blind you just as thoroughly as the dark.

An archivist needs a beam, not a floodlight. Narrow, focused, precise. One that does not reveal everything at once, but helps you find exactly what you need.

In SQL, that beam is called WHERE.

The SELECT clause answers the question:

What should be shown in the result?

And WHERE answers a different one:

Which rows should be included in the result at all?

That difference matters a great deal.

Take this query, for example:

SELECT name, price
FROM products;

It tells the database:

From the products table, show the name and price columns.

But add WHERE:

SELECT name, price
FROM products
WHERE price > 4000;

and the meaning changes:

Take the products table, check every row, and show only the products priced above 4000.

WHERE does not choose columns.
WHERE chooses rows.

The SELECT clause determines which columns appear in the result.
The WHERE clause determines which rows are included.

---

The basic shape of a query with WHERE

A query that filters rows usually looks like this:

SELECT column_1, column_2
FROM table_name
WHERE condition;

Let's go through it part by part:

SELECT name, price
FROM products
WHERE price > 4000;

• SELECT name, price — which columns to show in the result;
• FROM products — which table the rows come from;
• WHERE price > 4000 — which rows to keep;
• price > 4000 — the condition checked for every row.

The database checks each row in the products table and asks:

Is price > 4000 true for this row?

If the answer is TRUE, the row is included in the result.
If the answer is FALSE, the row is left out.

Important: WHERE does not delete rows from the table.
It simply leaves nonmatching rows out of that query's result.

The table stays exactly as it was.

---

A query always returns a new result table

When you write an SQL query, the database does not just "show you a slice of the table".
It builds a query result — a temporary table that you see on screen.

For example:

SELECT name, price
FROM products
WHERE price > 4000;

might return a result like this:

This result table has only two columns: name and price.

But the filtering was done on the price column.

So the price column served two purposes:

• it was used to evaluate the condition;
• it was included in the result.

The column used for filtering does not always have to appear in the result, though.

You can filter rows by a column that you do not show in the result:

SELECT name, price
FROM products
WHERE stock < 60;

Here, the result contains only name and price, but the database still uses the stock column to determine which rows to keep.

This is perfectly normal and very common.

WHERE can check a column even when that column is not in SELECT.

---

Comparison operators

Conditions in WHERE often use comparison operators.

They let you test the values in each row against your criteria.

The main comparison operators are:

Examples:

SELECT name, category
FROM products
WHERE category = 'Книги';

This query shows only the products in the Books category.

SELECT name, status
FROM orders
WHERE status <> 'cancelled';

This query shows the orders whose status is not cancelled.

SELECT name, price
FROM products
WHERE price <= 1500;

This query shows the products priced at 1500 or less.

Note that text values are written in single quotes:

WHERE category = 'Книги'

while numbers are usually written without quotes:

WHERE price > 4000

Do not write it like this:

WHERE category = Книги

SQL interprets the unquoted word Книги as an identifier, such as a column name, rather than as text. That is why text values need quotes.

---

Combining conditions: AND and OR

One condition is often not enough.

A buyer, for instance, might want to find products that are both expensive and nearly out of stock.

That is what the logical operators are for:

• AND — "and";
• OR — "or".

AND: every condition must be true

SELECT name, price, stock
FROM products
WHERE price > 4000 AND stock < 60;

This query shows only the rows where both conditions are true:

• price > 4000;
• stock < 60.

In other words, the product has to be both expensive and nearly out of stock.

If the price is above 4000 but there are 200 units in stock, the row does not pass.
If there are fewer than 60 units in stock but the price is 900, the row does not pass either.

AND is strict.
It lets a row through only when every condition is true.

---

OR: one condition is enough

SELECT name, category, price
FROM products
WHERE category = 'Книги' OR category = 'Игрушки';

This query shows products in either the Books category or the Toys category.

A row passes if at least one condition is true:

• the product is in the Books category;
• the product is in the Toys category.

OR casts a wider net than AND.
It lets more rows through because a single match is enough.

---

A walkthrough of the example query

Let's look at the query more closely:

SELECT name, price, stock
FROM products
WHERE price > 4000 AND stock < 60
ORDER BY price DESC;

It does four things:

1. Takes the rows from the products table.
2. Checks every row against the condition:

price > 4000 AND stock < 60

3. Keeps only the rows where the condition is true.
4. Shows the name, price and stock columns and sorts the result by price from highest to lowest.

In plain English, the query says:

Show the name, price and stock of the products in the products table,
but only for products that cost more than 4000 and have fewer than 60 units in stock,
and sort them from highest to lowest price.

A query like this no longer looks like an exercise — it looks like a real analytical query.

It answers a concrete business question:

Which expensive products are running low?

In the vault, rows like these are not just numbers.
They are signals.
Back in 2024, a buyer saw a result like this and knew what had to be done: restock before customers found empty shelves.

---

How the database "thinks" while it runs a query

Beginners often read SQL from top to bottom and assume the database runs SELECT first.

A query's logical processing order is different, though.

For this query:

SELECT name, price
FROM products
WHERE price > 4000
ORDER BY price DESC;

the rough logical order is:

1. FROM products
   The database identifies the table the rows come from.

2. WHERE price > 4000
   The database checks every row and keeps only the matching ones.

3. SELECT name, price
   The database selects the columns to include in the result.

4. ORDER BY price DESC
   The database sorts the final rows.

So WHERE does its work before the final result is built.

That is exactly why you can filter rows by a column you do not show in SELECT:

SELECT name
FROM products
WHERE price > 4000;

The result contains only name, but the database still uses price when filtering.

---

A story from the archive: the orders that are still waiting

The WHERE beam can pick out more than products and prices.

In the orders table, some orders have the status pending — "waiting".

SELECT id, customer_name, status
FROM orders
WHERE status = 'pending';

A query like this shows only the orders that are still waiting to be processed.

In an ordinary system, a manager might run it during a routine review of the order queue.

But in the archive of old Earth, these rows take on a different meaning.
Some of these orders have been waiting for almost a hundred and sixty years.
Nobody will ever receive them.
And yet the records still faithfully preserve their status:

pending

waiting.

QUERY: Here pending means "waiting forever". Do not dwell on it, cadet. Keep the beam moving.

---

AND vs OR precedence: the trap that stays silent

Logical operators have precedence: NOT binds tightest, then AND, and only then OR. As long as a condition has a single operator, you needn’t think about it. But mix AND and OR without parentheses and the beam starts pointing the wrong way:

-- We wanted: items from Books or Toys cheaper than 1500
SELECT name, category, price
FROM products
WHERE category = 'Книги' OR category = 'Игрушки' AND price < 1500;

The DBMS reads this as category = 'Книги' OR (category = 'Игрушки' AND price < 1500) — the price condition "stuck" to toys only. The output will include every book, "Algorithms in Practice" at 1690 among them, plus the cheap toys. The query is syntactically valid, runs without a single error — and returns the wrong answer. Bugs like this live in reports for years.

Parentheses restore the meaning:

SELECT name, category, price
FROM products
WHERE (category = 'Книги' OR category = 'Игрушки')
  AND price < 1500;

The archivist’s rule: whenever both AND and OR appear in one WHERE, always add parentheses, even when the precedence seems "obvious anyway". The parser won’t catch a logic error, and whoever reads your query a hundred years from now will thank you.

How operator precedence can cause bugs: row by row

Suppose the table contains these products:

A query without parentheses:

SELECT name, category, price
FROM products
WHERE category = 'Книги' OR category = 'Игрушки' AND price < 1500;

Because the AND operator has higher precedence than the OR operator, the database interprets the condition like this:

WHERE category = 'Книги'
   OR (category = 'Игрушки' AND price < 1500)

Let's check the rows:

Row 1: "Algorithms in Practice"

category = 'Книги' → TRUE

Since the first part is already TRUE, the row passes.
The price of 1690 does not matter because the price condition applies only to the toys.

Row 2: "Kids' drone"

category = 'Игрушки' → TRUE
price < 1500 → TRUE

Both conditions joined by AND are true, so the row passes.

Row 3: "Orbital station kit"

category = 'Игрушки' → TRUE
price < 1500 → FALSE

The AND expression evaluates to FALSE, so the row does not pass.

Row 4: "Power cable"

category = 'Книги' → FALSE
category = 'Игрушки' → FALSE

The row does not pass.

The result contains an unintended row: an expensive book slipped through even though we intended to return only products cheaper than 1500.

---

Parentheses restore the intended meaning

If we want products in either the Books category or the Toys category, all priced below 1500, we have to group the category conditions explicitly:

SELECT name, category, price
FROM products
WHERE (category = 'Книги' OR category = 'Игрушки')
  AND price < 1500;

Now the condition reads like this:

First check that the product is a book or a toy.
Then check that its price is below 1500.

Parentheses make the intended grouping clear to both the database and anyone reading the query.

The archivist's rule:

Whenever a WHERE clause contains both AND and OR, always add parentheses.

Even when the precedence seems obvious.

The parser will not catch a logical mistake.
But whoever reads your query a hundred years from now will thank you.

---

An important trap: do not test NULL with =

SQL has a special value, NULL.

NULL does not mean zero, an empty string, or the word "no".
NULL means:

the value is unknown or missing.

An order, for instance, may not have a delivery date yet:

delivered_at = NULL

Beginners often try to find such rows with a query like this:

SELECT id, status, delivered_at
FROM orders
WHERE delivered_at = NULL;

But that is the wrong test.

In SQL, comparing a value to NULL with = does not produce TRUE.
That is because NULL represents an unknown value.

Write it this way instead:

SELECT id, status, delivered_at
FROM orders
WHERE delivered_at IS NULL;

And if you need the rows where the value is present:

SELECT id, status, delivered_at
FROM orders
WHERE delivered_at IS NOT NULL;

The key idea:

• for ordinary values we use =, <>, <, >, <=, >=;
• for NULL we use IS NULL and IS NOT NULL.

We will cover NULL in more detail later, but it is worth remembering right now:
WHERE column = NULL is almost always a mistake.

---

Common beginner mistakes

Mistake 1. Thinking that WHERE picks columns

The incorrect idea:

WHERE is there to pick the fields you want.

The correct idea:

WHERE is there to pick the rows you want.

Columns are chosen by SELECT.

SELECT name, price
FROM products
WHERE category = 'Книги';

Here:

• SELECT name, price chooses the columns;
• WHERE category = 'Книги' chooses the rows.

---

Mistake 2. Writing == instead of =

Incorrect:

WHERE status == 'paid'

Correct:

WHERE status = 'paid'

---

Mistake 3. Forgetting the quotes around text

Incorrect:

WHERE status = paid

Correct:

WHERE status = 'paid'

---

Mistake 4. Mixing AND and OR without parentheses

Dangerous:

WHERE status = 'pending' OR status = 'paid' AND total > 5000;

Better:

WHERE (status = 'pending' OR status = 'paid')
  AND total > 5000;

---

Mistake 5. Testing NULL with =

Incorrect:

WHERE delivered_at = NULL

Correct:

WHERE delivered_at IS NULL

---

Mistake 6. Putting WHERE after ORDER BY

Incorrect:

SELECT name, price
FROM products
ORDER BY price DESC
WHERE price > 4000;

Correct:

SELECT name, price
FROM products
WHERE price > 4000
ORDER BY price DESC;

In a standard query, WHERE is written after FROM but before ORDER BY.

---