Intro to SQLfor Organizing
Module 5 · Filtering and Sorting: WHERE, ORDER BY, LIMIT › 5.1 WHERE: the gatekeeper
Subsection 5.1

WHERE: the gatekeeper

~4 min

Reading

The WHERE clause filters rows.

It limits the number of rows returned by your query by applying a logical statement or comparison. BigQuery checks each row. If the row matches the condition, it returns. If not, it stays out of the result.

Basic pattern:

SELECT column1, column2, columnN
FROM `project.dataset.table`
WHERE condition;

Example from the slide deck:

SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE vb_voterbase_age_bucket = '18-29';

This query returns rows where the vb_voterbase_age_bucket value equals '18-29'.

Notice the quotes around '18-29'. That value is a string literal. Strings need quotes. Numeric values usually do not.

Common WHERE conditions include:

  • Equality: WHERE X = Y
  • Not equal: WHERE X != Y
  • Greater than: WHERE X > 3
  • String matching: WHERE name LIKE '%Maria%'
  • Combined logic: AND, OR, and NOT

WHERE is one of the biggest shifts from browsing data to asking precise questions.

Learner action

Write one WHERE clause that matches your organizing question.

From Rosario's deck:

"The WHERE clause is used to limit the amount of rows returned from your query. A logical statement or comparison is passed to the WHERE clause, and any rows that match those conditions get returned."

Pattern

SELECT column1, column2, … columnN
FROM `project.dataset.table`
WHERE condition;

Worked example from the deck

SELECT *
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
WHERE vb_voterbase_age_bucket = '18-29';

The quote rule: '18-29' is in single quotes because it's a string literal. Numbers do not need quotes (year >= 2020). Strings always do.

10,000,000 rows WHERE state = 'GA' 250,000 rows WHERE year >= 2020 30,000 rows
Diagram 5.1 · Filtering as a funnel. Each WHERE condition narrows the result. Stack them with AND to keep going down.

WHERE is the gatekeeper. It runs once per row, keeps the rows where the condition is true, drops the rest. Filter the voter file to just one county.

Action: Write one WHERE condition that matches your Module 1 question. Run it. Watch the row count drop.