RizTech Academy logo
RizTech Academy
Your First QueriesLesson 2 of 525 min

WHERE: filtering rows

WHERE decides which rows come back. It is one keyword and a condition, and the condition is where every interesting mistake lives.

The basics

SELECT title, copies FROM books WHERE shelf = 'F-05';

          title          | copies
-------------------------+--------
 Interpreter of Maladies |      3
 The Namesake            |      4
 Unaccustomed Earth      |      2
(3 rows)

WHERE runs before SELECT picks columns, which is why you can filter on a column you are not selecting:

SELECT title FROM books WHERE copies > 3;

That is the execution order from the previous lesson doing something visible.

The comparison operators

=        equal            <>  or  !=   not equal
<   >    less, greater    <=  >=       or equal

<> is the standard spelling of "not equal"; != works in PostgreSQL and most others. Pick one and be consistent — this course uses <>.

There is no == in SQL. Assignment and comparison are both =, because SQL has no assignment in an expression.

SELECT title FROM books WHERE published < DATE '1950-01-01';
SELECT name  FROM members WHERE membership <> 'standard';
SELECT title FROM books WHERE price_paise >= 40000;

Comparison works on text and dates as well as numbers. Text compares by collation — roughly dictionary order — and dates compare chronologically, so < on a date means earlier.

Write date literals as DATE '1950-01-01'. The DATE prefix makes the type explicit and the YYYY-MM-DD form is unambiguous. '01-02-1950' is a different day in India than in the United States, and a database that has to guess will eventually guess differently from you.

Combining conditions

SELECT title FROM books WHERE shelf = 'F-01' AND copies > 2;
SELECT title FROM books WHERE shelf = 'F-01' OR shelf = 'F-02';
SELECT title FROM books WHERE NOT (shelf = 'F-01');

The precedence trap

AND binds tighter than OR. This is the single most common WHERE bug and it is completely silent — you get rows, just not the ones you meant.

SELECT count(*) FROM books WHERE shelf = 'F-01' OR shelf = 'F-02' AND copies > 2;

 count
-------
     4

SELECT count(*) FROM books WHERE (shelf = 'F-01' OR shelf = 'F-02') AND copies > 2;

 count
-------
     3

The first reads as shelf = 'F-01' OR (shelf = 'F-02' AND copies > 2) — so it returns every F-01 book regardless of how many copies there are. Four rows instead of three, and nothing warns you.

Write the brackets even when you are sure. They cost nothing and they say what you meant.

IN, which is OR written tidily

SELECT name, membership FROM members
WHERE membership IN ('student', 'senior')
ORDER BY name LIMIT 5;

     name      | membership
---------------+------------
 Aditya Kale   | senior
 Amit Ranade   | student
 Anjali Gokhale| student
 Arjun Pawar   | senior
 Deepa Apte    | student
(5 rows)

IN (a, b, c) is exactly = a OR = b OR = c, and it is far easier to read once there are more than two. NOT IN is the negation — and it has a serious trap when NULL is involved, which is the next lesson.

IN also takes a subquery:

SELECT title FROM books
WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL);

"Books that are currently out." Module 5 covers subqueries properly.

BETWEEN

SELECT title, price_paise FROM books
WHERE price_paise BETWEEN 40000 AND 50000
ORDER BY price_paise;

          title          | price_paise
-------------------------+-------------
 The Hungry Tide         |       40500
 An Equal Music          |       41000
 Unaccustomed Earth      |       42500
 The Inheritance of Loss |       44000
 The God of Small Things |       45000
 Sea of Poppies          |       48500
(6 rows)

BETWEEN is inclusive at both ends — >= 40000 AND <= 50000. People assume it is exclusive at the top about half the time.

That inclusiveness is a genuine problem with timestamps:

-- WRONG: misses anything after midnight on the 30th
WHERE borrowed_at BETWEEN '2026-09-01' AND '2026-09-30'

-- right
WHERE borrowed_at >= DATE '2026-09-01' AND borrowed_at < DATE '2026-10-01'

A timestamp of 2026-09-30 14:22 is after 2026-09-30 00:00:00, so BETWEEN excludes almost the whole last day. For dates with times, use >= and < on the day after. It is the pattern to use by reflex.

Filtering on a computed value

SELECT title, price_paise FROM books WHERE price_paise / 100 > 400;

That works, and it has a cost worth knowing now: an expression around a column usually prevents the database using an index on it. WHERE price_paise > 40000 can use an index; WHERE price_paise / 100 > 400 may not. Module 7 explains why. The habit to form is to put the column alone on one side when you can.

And you cannot use a SELECT alias in WHERE:

SELECT title, price_paise / 100 AS rupees FROM books WHERE rupees > 400;
-- ERROR:  column "rupees" does not exist

Because WHERE runs before SELECT. The alias does not exist yet. Repeat the expression, or use a subquery.

Boolean columns

SELECT title FROM books WHERE copies > 0;

If a column is already boolean, compare it to nothing:

WHERE is_active              -- good
WHERE is_active = true       -- works, and is noise
WHERE is_active IS TRUE      -- also works; handles NULL differently
WHERE NOT is_active          -- the negation

= true is redundant. The one case where IS TRUE differs is when the column can be NULL — next lesson.

The WHERE you must not forget

DELETE FROM loans;              -- every loan, gone
UPDATE books SET copies = 0;    -- every book, zero copies

Both are valid SQL and both run instantly. There is no confirmation.

The habit that saves you: write the WHERE first. Type DELETE FROM loans WHERE id = 42; as one thought, never DELETE FROM loans and then go back to add the condition, because that is the version that gets executed by accident.

And test with SELECT first. Swap DELETE for SELECT *, look at what comes back, then change the word. Module 6 covers this properly, and it is worth knowing on day two because the mistake is available from day two.

Check your work

Why you can filter on a column you did not select. WHERE runs before SELECT.

The not-equal operator. <> is standard; != also works. There is no ==.

Why to write DATE '1950-01-01'. Explicit type and unambiguous order — 01-02 is a different day in different countries.

The precedence rule. AND binds tighter than OR, silently. Write brackets.

What IN is. OR written tidily — and it takes a subquery.

Whether BETWEEN includes its endpoints. Both of them.

Why BETWEEN is wrong for timestamps. It excludes almost all of the last day. Use >= and < on the following day.

The cost of wrapping a column in an expression. It usually prevents an index being used.

Why an alias does not work in WHERE. WHERE runs before SELECT creates it.

How to compare a boolean. Just WHERE is_active. = true is noise.

The two habits around DELETE and UPDATE. Write the WHERE in the same thought, and test with SELECT first.

Practice

  1. Find every book on shelf F-08.
  2. Find every book published before 1950, ordered by date.
  3. Find members who are not on the standard membership.
  4. Run the precedence example both ways and explain the difference in row count.
  5. Write a three-condition WHERE with mixed AND and OR. Predict the result, then add brackets and check you were right.
  6. Rewrite a four-way OR as an IN.
  7. Use BETWEEN for books priced ₹300 to ₹400. Confirm the endpoints are included by checking a book at exactly 40000 paise... and by finding one.
  8. Write a date range with BETWEEN, then rewrite it with >= and <. Explain when they would differ.
  9. Try to use a SELECT alias in WHERE and read the error.
  10. Write WHERE price_paise / 100 > 400 and then the version that leaves the column alone.
  11. Find loans that are not yet returned and were due before today.
  12. Write a DELETE for one loan as a SELECT first, check the rows, then convert it. Then roll it back — BEGIN; DELETE ...; ROLLBACK;.

Official documentation

Next: ordering and paging.

Stuck on this lesson?

Being stuck is part of it — but being stuck alone for three days is not. Our internship programme pairs this curriculum with code review and one-to-one help from working developers, and it is free.

About the internship