The WHERE clause — SQL: where & operators

The bouncer at the door

Imagine a nightclub with a thousand people inside. You don't want to talk to everyone: you want to find your friend who's wearing a red jacket. Instead of scanning every face, you tell the bouncer: "Only let through people in red jackets." The bouncer filters the crowd, and only matching people reach you.

That bouncer is the WHERE clause. In the previous course, you learned SELECT and FROM: how to choose which columns to display and which table to read from. But without WHERE, every query returns every row in the table. That's like asking the bouncer to let everyone through.

WHERE is the single most important clause for real-world applications. Almost no production query runs without it.

How WHERE works in the query pipeline

Recall the query pipeline from the previous course: Parser → Planner → Executor. The WHERE clause affects all three stages:

SELECT name, email FROM users WHERE active = true;

flowchart TB
  A["Parser<br/>converts WHERE active = true into a filter node in the AST"] --> B["Planner<br/>checks whether an index exists on active.<br/>Yes -> Index Scan (fast). No -> Sequential Scan (reads every row)"]
  B --> C["Executor<br/>evaluates the condition for each row.<br/>Only rows where active = true pass through; rejected rows are discarded"]

The executor applies your WHERE condition as a predicate: a true/false test evaluated against each candidate row. If the predicate returns true, the row is included in the result set. If false or NULL, the row is excluded.

This is a key insight: WHERE doesn't search for data. It eliminates data. Every row starts as a candidate; WHERE decides which candidates survive.

Comparison operators

SQL provides six comparison operators that work with numbers, strings, dates, and most other data types:

Operator	Meaning	Example
`=`	Equal to	`WHERE level = 0`
`!=` or `<>`	Not equal to	`WHERE status != 'completed'`
`<`	Less than	`WHERE position < 5`
`>`	Greater than	`WHERE position > 3`
`<=`	Less than or equal	`WHERE level <= 1`
`>=`	Greater than or equal	`WHERE level >= 2`

Notice that SQL uses = for comparison, not ==. This is a common trap for programmers coming from Ruby, JavaScript, or Python, where = means assignment and == means comparison. In SQL, = always means comparison: there is no assignment operator in a SELECT query.

Filtering by numbers

Let's use this app's own tables. The courses table has a level column stored as an integer (0 = intern, 1 = junior, 2 = mid, 3 = senior). Titles live in a JSON column, title, with one key per language, so title->>'en' reads the English title as text:

-- Find all junior-level courses
SELECT title->>'en' AS title, level
FROM courses
WHERE level = 1
ORDER BY position;

Result:

          title          | level
-------------------------+-------
 Raw SQL: select & from  |     1
 SQL: where & operators  |     1
 ...
(101 rows)

The ActiveRecord equivalent:

# ActiveRecord
Catalog::Course.where(level: :junior)

# SQL generated by Rails
SELECT "courses".* FROM "courses" WHERE "courses"."level" = 1

Notice that Rails translates the symbol :junior into the integer 1 because Catalog::Course uses enum :level. ActiveRecord never pastes raw input into the SQL: with prepared statements on, the value 1 travels separately from the SQL text; with them off (as in this app's development log), Rails quotes and escapes it. Either way, a hash condition is safe from SQL injection.

Filtering by strings

String comparisons use single quotes in SQL. Double quotes are reserved for identifiers (table and column names):

-- Find a user by email
SELECT id, name, email
FROM users
WHERE email = '[email protected]';

In ActiveRecord:

Identity::User.where(email: '[email protected]')
# SQL: SELECT "users".* FROM "users" WHERE "users"."email" = '[email protected]'

String comparisons in PostgreSQL are case-sensitive by default. 'Alice' and 'alice' are different values. If you need case-insensitive matching, you'll learn about ILIKE in Lesson 3.

Common mistake: Using double quotes around string values.

-- WRONG: double quotes mean a column name
WHERE email = "[email protected]"
-- PostgreSQL reads this as: WHERE email = (column named [email protected])
-- Error: column "[email protected]" does not exist

-- CORRECT: single quotes for string values
WHERE email = '[email protected]'

Filtering with inequality operators

Inequality operators are essential for range queries: finding records above, below, or outside a threshold:

-- Find lessons after position 3 in a course
SELECT title->>'en' AS title, position
FROM lessons
WHERE course_id = 5
  AND position > 3
ORDER BY position;

Result:

         title             | position
---------------------------+----------
 IN, BETWEEN & Ranges      |        4
 NULL Operators             |        5
 Dates & Times              |        6
 Indexes & Sargability      |        7
(4 rows)

ActiveRecord equivalent:

Catalog::Lesson.where(course_id: 5).where('position > ?', 3).order(:position)
# SQL: SELECT "lessons".* FROM "lessons"
#      WHERE "lessons"."course_id" = 5 AND (position > 3)
#      ORDER BY "lessons"."position" ASC

Notice the ? placeholder: this is a parameterized query. ActiveRecord replaces ? with the value 3, properly escaped. Never interpolate values directly into SQL strings: that creates SQL injection vulnerabilities.

The WHERE clause and NULL

One critical rule: you cannot use = or != to compare with NULL. This query returns zero rows, even if some users have no name:

-- WRONG: returns no rows
SELECT * FROM users WHERE name = NULL;

-- CORRECT: use IS NULL
SELECT * FROM users WHERE name IS NULL;

This happens because SQL uses three-valued logic: every comparison with NULL returns NULL (not true, not false). And WHERE only keeps rows where the condition is true. We will explore NULL operators in depth in Lesson 5.

Common mistakes

Mistake: Writing WHERE active = true for boolean columns and assuming it's the only way.
The nuance: In PostgreSQL, WHERE active, WHERE active = true, and WHERE active IS TRUE all work, but they behave differently with NULL values. WHERE active and WHERE active = true both exclude NULLs. WHERE active IS TRUE also excludes NULLs. But WHERE active IS NOT FALSE includes NULLs. Be explicit about your intent.

Mistake: Forgetting that != does not match NULL values.
Why it's wrong: WHERE status != 'completed' will NOT return rows where status is NULL, because NULL != 'completed' evaluates to NULL, not true. If you want those rows too, use: WHERE status != 'completed' OR status IS NULL.

Summary

Concept	Key Point
WHERE clause	Filters rows by evaluating a predicate: only rows returning `true` are included
Comparison operators	`=`, `!=`/`<>`, `<`, `>`, `<=`, `>=`: work with numbers, strings, and dates
String values	Always use single quotes (`'text'`), never double quotes (those are for identifiers)
NULL comparisons	`= NULL` never works: use `IS NULL` or `IS NOT NULL` instead
ActiveRecord `.where`	Generates parameterized `WHERE` clauses: prevents SQL injection automatically
Query planner impact	WHERE conditions determine whether the planner uses an Index Scan or Sequential Scan
Parameterized queries	Use `?` placeholders in ActiveRecord: never interpolate user input into SQL strings