Lesson 27 · Filtering with WHERE - Build a SQL Database

Filtering with WHERE

A query that returns every row is rarely what you want. Today you add the filter operator, keeping only the rows whose WHERE predicate evaluates to true.

The goal

Execute a SELECT with a WHERE clause so only rows satisfying the predicate are returned.

Start here - the target

TO DO

Scenario: Filtering rows by a WHERE predicate

Givena users table with rows [1, "alice", 30], [2, "bob", 25], [3, "carol", 30]

When"SELECT name FROM users WHERE age > 27" is executed

Thenthe result rows are ["alice"] and ["carol"] in that order

And"... WHERE age = 25" returns only ["bob"]

Background

The filter is the operator that makes queries selective. It sits between the scan and the projection: for each scanned row, evaluate the WHERE predicate (lesson 25) against the table’s schema - the full set of columns, since the predicate may test a column the projection drops - and keep the row only if the result is true. Rows that fail the test are discarded; the survivors flow on to projection.

Order matters here: filter before you project, because `SELECT name … WHERE age

27filters onage` even though the result never shows it. This is the classic scan-filter-project pipeline, and it is why every operator speaks the same result set - each is just a function from rows to rows. With filtering in place, the engine runs genuinely useful queries; tomorrow it reads them from a live prompt.

Make it work

// between Scan and projection:
//   if s.Where != nil, keep only rows where Eval(s.Where, row, schema)
//   is true; evaluate against the table's schema, before projecting

CheckpointDONE

SELECT ... WHERE returns just the matching rows. Commit and stop here.