Session 5 · Filtering rows with WHERE#

The big idea#

A table can have millions of rows and you usually want a few. WHERE keeps only the rows that match a condition. You build conditions with comparisons (=, >), logic (AND, OR, NOT) and helpers (IN, BETWEEN, LIKE, IS NULL). LIMIT then trims how many come back.

Everyday example: a shop’s search page. “Phones, under 20,000, brand Samsung or Redmi.” Each filter you tick throws away products until a short list is left. SQL filters do exactly this.

Filters narrow the rows Eleven movies go in; WHERE genre = 'Action' keeps four; AND year > 2000 keeps three. 11 rows all movies 4 rows genre = 'Action' 3 rows AND year > 2000 each condition only removes rows, never adds
Eleven movies go in; WHERE genre = 'Action' keeps four; AND year > 2000 keeps three.

The queries#

All of these ran against the practice database. Read the SQL, then the output below it.

\echo '-- WHERE'
SELECT title, year FROM movies WHERE genre = 'Action';
\echo '-- AND'
SELECT title, year FROM movies WHERE genre = 'Action' AND year > 2000;
\echo '-- OR'
SELECT title, genre FROM movies WHERE genre = 'Crime' OR genre = 'Comedy';
\echo '-- IN'
SELECT title, year FROM movies WHERE year IN (1995, 2021);
\echo '-- BETWEEN is inclusive'
SELECT title, rating FROM movies WHERE rating BETWEEN 8.0 AND 8.5 ORDER BY rating;
\echo '-- LIKE and ILIKE'
SELECT title FROM movies WHERE title LIKE 'S%';
SELECT title FROM movies WHERE title ILIKE '%pottru';
\echo '-- = NULL never matches. IS NULL does'
SELECT title FROM movies WHERE genre = NULL;
SELECT title FROM movies WHERE genre IS NULL;
\echo '-- LIMIT and OFFSET, FETCH'
SELECT title, rating FROM movies WHERE rating IS NOT NULL ORDER BY rating DESC LIMIT 3;
SELECT title, rating FROM movies WHERE rating IS NOT NULL ORDER BY rating DESC OFFSET 3 FETCH FIRST 2 ROWS ONLY;
\echo '-- AND binds tighter than OR: parentheses change the answer'
SELECT title, genre, year FROM movies WHERE genre = 'Drama' OR genre = 'Action' AND year > 2015 ORDER BY year;
SELECT title, genre, year FROM movies WHERE (genre = 'Drama' OR genre = 'Action') AND year > 2015 ORDER BY year;

-- WHERE
 title  | year 
--------+------
 Baasha | 1995
 Sivaji | 2007
 Kaithi | 2019
 Vikram | 2022
(4 rows)

-- AND
 title  | year 
--------+------
 Sivaji | 2007
 Kaithi | 2019
 Vikram | 2022
(3 rows)

-- OR
  title  | genre  
---------+--------
 Nayakan | Crime
 Muthu   | Comedy
(2 rows)

-- IN
 title  | year 
--------+------
 Baasha | 1995
 Muthu  | 1995
 Karnan | 2021
(3 rows)

-- BETWEEN is inclusive
   title    | rating 
------------+--------
 Karnan     |    8.0
 Baasha     |    8.2
 Vikram     |    8.3
 Kaithi     |    8.4
 Thalapathi |    8.5
(5 rows)

-- LIKE and ILIKE
      title      
-----------------
 Sivaji
 Soorarai Pottru
(2 rows)

      title      
-----------------
 Soorarai Pottru
(1 row)

-- = NULL never matches. IS NULL does
 title 
-------
(0 rows)

      title       
------------------
 Untitled Project
(1 row)

-- LIMIT and OFFSET, FETCH
       title       | rating 
-------------------+--------
 Nayakan           |    9.0
 Pariyerum Perumal |    8.7
 Soorarai Pottru   |    8.6
(3 rows)

   title    | rating 
------------+--------
 Thalapathi |    8.5
 Kaithi     |    8.4
(2 rows)

-- AND binds tighter than OR: parentheses change the answer
       title       | genre  | year 
-------------------+--------+------
 Thalapathi        | Drama  | 1991
 Pariyerum Perumal | Drama  | 2018
 Kaithi            | Action | 2019
 Soorarai Pottru   | Drama  | 2020
 Karnan            | Drama  | 2021
 Vikram            | Action | 2022
(6 rows)

       title       | genre  | year 
-------------------+--------+------
 Pariyerum Perumal | Drama  | 2018
 Kaithi            | Action | 2019
 Soorarai Pottru   | Drama  | 2020
 Karnan            | Drama  | 2021
 Vikram            | Action | 2022
(5 rows)

What to notice#

  • AND narrows, OR widens. Four Action movies; adding year > 2000 leaves three.

  • IN (1995, 2021) is shorthand for year = 1995 OR year = 2021.

  • BETWEEN 8.0 AND 8.5 includes both ends. Karnan (8.0) and Thalapathi (8.5) are in the result.

  • LIKE 'S%': % means “any characters”, _ means exactly one. LIKE is case-sensitive; ILIKE is not, which is why ILIKE '%pottru' matched “Soorarai Pottru”.

  • = NULL returns nothing, even though a row has NULL. NULL means “unknown”, and “unknown equals unknown” is not true. Use IS NULL / IS NOT NULL.

  • LIMIT 3 gives the top three; OFFSET 3 FETCH FIRST 2 ROWS ONLY skips three and takes the next two (the standard SQL spelling of LIMIT 2 OFFSET 3). Always pair them with ORDER BY.

  • AND is evaluated before OR. The last two queries use the same words but return 6 and 5 rows. In the first, Thalapathi (a 1991 drama) sneaks in because it only had to satisfy genre = 'Drama'. Parentheses make your intent explicit.

A mental model for conditions#

Think of WHERE as a test the database runs on each row, one at a time. The row either passes (the condition is true) and goes into the result, or it fails and is skipped. Three outcomes are possible, not two: true, false, and unknown. A comparison with NULL is unknown, and WHERE only keeps rows where the answer is true. That is the whole reason genre = NULL returns nothing and why NOT IN misbehaves around NULL.

When a query gives surprising rows, test the pieces. Run SELECT title, genre, year FROM movies first, look at the data, then add one condition at a time and watch how the row count shrinks (11, then 4, then 3 in the diagram above). Building filters step by step is quicker than guessing.

Common mistakes#

  • WHERE genre = NULL. Always empty. Use IS NULL.

  • Forgetting parentheses around mixed AND/OR, as the last two queries show.

  • LIKE '%word' on a huge table. A leading % cannot use a normal index and is slow.

  • Using double quotes for text. WHERE genre = "Action" fails; text values use single quotes.

  • LIMIT without ORDER BY. You get some rows, not the top ones.

  • Using NOT IN with a NULL in the list. The result is empty. Prefer NOT EXISTS.

Hands-on exercises#

Exercise 1. Find movies from before 2000 that are not Drama.

Solution 
SELECT title, genre, year FROM movies WHERE year < 2000 AND genre <> 'Drama' ORDER BY year;

Expected rows: Nayakan (Crime), Baasha (Action) and Muthu (Comedy).

Exercise 2. Find every movie whose title contains the letters ra anywhere, ignoring case.

Solution 
SELECT title FROM movies WHERE title ILIKE '%ra%' ORDER BY title;

%ra% matches “ra” at any position. It returns two rows: Soorarai Pottru and Vikram.

Exercise 3. Show the third and fourth best rated movies.

Solution 
SELECT title, rating FROM movies
WHERE rating IS NOT NULL
ORDER BY rating DESC
OFFSET 2 FETCH FIRST 2 ROWS ONLY;

Skip two rows, take two: Soorarai Pottru (8.6) and Thalapathi (8.5). The IS NOT NULL filter keeps the unrated movie out of the ranking.

Practice#

Take the Session 5 quiz. For more operators (SIMILAR TO, ESCAPE) see the reference pages under Filtering Data.