SQL Tutorial

Filtering Techniques

📚 Schema reference · towns & counties 3 tables
fips
  • fips
  • name
  • state
pnw_counties
  • county_id
  • county
  • state
  • fips_short
  • county_seat
  • county_seat_town_id
  • year_established
  • origin
  • etymology
  • population_2022
  • land_area_sq_mi
pnw_towns
  • town_id
  • town
  • state
  • primary_county
  • secondary_county
  • tertiary_county
  • primary_county_id
  • secondary_county_id
  • tertiary_county_id
  • population_2020_census
  • population_2010_census
  • land_area_sq_mi

Filtering rows/records

The WHERE clause is your primary tool for finding specific records. Think of it as asking questions of your data.

Filtering 1: Which towns qualify as “cities” (population over 50,000)?

Scenario: You’re researching urban development and need to identify the larger municipalities. Show each town with its state and population_2020_census, largest first.

Filtering 2: Find large Oregon cities for a state-specific grant

Scenario: An Oregon-only grant targets cities over 50,000 people. Who qualifies? Show each town and its population_2020_census, largest first — since every result is in Oregon, the state column is deliberately left out.

Filtering 3: Find either large cities OR any Oregon town

Scenario: A regional initiative includes all Oregon towns plus major cities from Washington. The OR operator creates a union of conditions. Show town, state, and population_2020_census.

Filtering 4: Find historically significant, populous counties

Scenario: A heritage tourism initiative targets counties established before 1860 that now have substantial populations (over 50,000). Show each county with its state, year_established, and population_2022.

The next example uses BETWEEN, and Filtering 11 uses IN. Both are shorthand for comparisons you already know. Scroll to see exactly which rows each one keeps, including the rows that sit on the boundary.

BETWEEN includes both ends, and IN checks a list

-- a range
 WHERE pop BETWEEN 10000 AND 50000
-- a list
 WHERE town IN ('Astoria', 'Bend', 'Yakima')

Scroll to build the diagram, one step at a time.

townpopTillamook5200Astoria10000Newport10300Pullman32000Olympia50000Bend99000pop BETWEEN 10000 AND 50000WHEREdropkeepkeepkeepkeepdroppop >= 10000 AND pop <= 50000WHEREdropkeepkeepkeepkeepdroptown IN ('Astoria', 'Bend', 'Yakima')WHEREdropkeepdropdropdropkeep
Step 1 · the rows Six towns sorted by population. Two of them sit exactly on a round number: Astoria at 10000 and Olympia at 50000.
Step 2 · BETWEEN is inclusive BETWEEN 10000 AND 50000 keeps both endpoints. Astoria (10000) and Olympia (50000), ringed in blue, are in the range.
Step 3 · the same thing, spelled out BETWEEN is shorthand for >= low AND <= high, and the keep/drop column is identical. If you need to exclude an endpoint, write the comparisons yourself with > or <.
Step 4 · IN checks a list IN (...) keeps a row when its value equals any item in the list, replacing a chain of ORs. 'Yakima' is in the list but not in the table, which is fine: it matches no row.
Text version of this diagram
  1. Step 1 · the rows. Six towns sorted by population. Two of them sit exactly on a round number: Astoria at 10000 and Olympia at 50000.
  2. Step 2 · BETWEEN is inclusive. BETWEEN 10000 AND 50000 keeps both endpoints. Astoria (10000) and Olympia (50000), ringed in blue, are in the range.
  3. Step 3 · the same thing, spelled out. BETWEEN is shorthand for >= low AND <= high, and the keep/drop column is identical. If you need to exclude an endpoint, write the comparisons yourself with > or <.
  4. Step 4 · IN checks a list. IN (...) keeps a row when its value equals any item in the list, replacing a chain of ORs. 'Yakima' is in the list but not in the table, which is fine: it matches no row.

Filtering 5: Find mid-sized towns (population between 10,000 and 50,000)

Scenario: A “Main Street” revitalization program targets mid-sized towns. BETWEEN provides a clean way to specify ranges. Show town, state, and population_2020_census.

Filtering 6: Find compact towns (small land area)

Scenario: Urban planners studying density want towns under 2 square miles. Show each town with its state, land_area_sq_mi, and population_2020_census.

Before the exercise, it is worth seeing exactly what those parentheses change. AND binds tighter than OR, so an unparenthesized mix rarely reads the way it looks. Scroll through both parses of the same three conditions.

AND, OR, and what the parentheses change

-- A: pop>100000 AND region='OR' OR coastal
-- B: pop>100000 AND (region='OR' OR coastal)

Scroll to build the diagram, one step at a time.

conditions on each towntownpop>100kregion='OR'coastalPortland✓✓✗Astoria✗✓✓Seattle✓✗✗Ilwaco✗✗✓Tacoma✓✗✓Twisp✗✗✗A AND B OR CkeepkeepdropkeepkeepdropA AND (B OR C)keepdropdropdropkeepdrop
Step 1 · the conditions Three conditions per town: is it big (pop>100k), is it in Oregon, and is it coastal. Every town is a different mix of ✓ and ✗, from Portland (big and in Oregon) down to Twisp, which fails all three.
Step 2 · AND binds tighter than OR Without parentheses, SQL reads A AND B OR C as (A AND B) OR C: AND groups first. Any coastal town passes on C alone, so small coastal Astoria and Ilwaco are kept. Ilwaco is not even in Oregon: coastal was enough.
Step 3 · parentheses re-group it Add parentheses: A AND (B OR C). Now every kept row must be big. Astoria and Ilwaco fail pop>100k, so they flip to drop. Tacoma is coastal too, but it is big, so it stays. Same three conditions, different answer.
Step 4 · the lesson Mixed AND/OR without parentheses almost never means what it looks like. When both appear in one WHERE, parenthesize the intent so the reader (and SQL) agree.
Text version of this diagram
  1. Step 1 · the conditions. Three conditions per town: is it big (pop>100k), is it in Oregon, and is it coastal. Every town is a different mix of ✓ and ✗, from Portland (big and in Oregon) down to Twisp, which fails all three.
  2. Step 2 · AND binds tighter than OR. Without parentheses, SQL reads A AND B OR C as (A AND B) OR C: AND groups first. Any coastal town passes on C alone, so small coastal Astoria and Ilwaco are kept. Ilwaco is not even in Oregon: coastal was enough.
  3. Step 3 · parentheses re-group it. Add parentheses: A AND (B OR C). Now every kept row must be big. Astoria and Ilwaco fail pop>100k, so they flip to drop. Tacoma is coastal too, but it is big, so it stays. Same three conditions, different answer.
  4. Step 4 · the lesson. Mixed AND/OR without parentheses almost never means what it looks like. When both appear in one WHERE, parenthesize the intent so the reader (and SQL) agree.

Filtering 7: Complex criteria with parentheses

Scenario: Find either (a) old Washington counties established before 1860, or (b) any Oregon county with population over 200,000. Parentheses control the logic. Show county, state, year_established, and population_2022.

Filtering text

Pattern matching with LIKE lets you search for partial text matches using wildcards: % matches any sequence of characters, _ matches exactly one character.

Scroll to see which letters of each name a pattern accounts for.

LIKE: what % and _ stand for

SELECT town
  FROM towns
 WHERE town LIKE '%ville';

Scroll to build the diagram, one step at a time.

typed letters, matched exactlycovered by %one _ eachtownWilsonvilleCoupevillePortlandAshlandSalemSandySeattletown LIKE '%ville'WilsonvilleCoupevillePortlandAshlandSalemSandySeattleWHEREkeepkeepdropdropdropdropdroptown LIKE '%land%'WilsonvilleCoupevillePortlandAshlandSalemSandySeattleWHEREdropdropkeepkeepdropdropdroptown LIKE 'S____'WilsonvilleCoupevillePortlandAshlandSalemSandySeattleWHEREdropdropdropdropkeepkeepdrop
Step 1 · text, letter by letter LIKE compares text to a pattern. Letters you type must match exactly. Two wildcard characters stand in for the parts you do not care about: % and _.
Step 2 · ends with In '%ville' the % covers any run of characters (blue), then ville must finish the name (yellow). Wilsonville and Coupeville pass. Nothing else ends that way.
Step 3 · contains A % on both sides, '%land%', finds land anywhere. In Portland and Ashland the trailing % covers nothing at all, and that is allowed: % means zero or more characters.
Step 4 · exactly one character Each _ stands for exactly one character (purple). 'S____' is an S plus four more: five letters in total. Salem and Sandy fit. Seattle starts with S but has seven letters, so it fails.
Step 5 · choosing a wildcard Use % when the length does not matter and _ when it does. With no wildcard, LIKE is an equality test. In SQLite, LIKE ignores upper and lower case for plain letters; other databases may not, so check before you rely on it.
Text version of this diagram
  1. Step 1 · text, letter by letter. LIKE compares text to a pattern. Letters you type must match exactly. Two wildcard characters stand in for the parts you do not care about: % and _.
  2. Step 2 · ends with. In '%ville' the % covers any run of characters (blue), then ville must finish the name (yellow). Wilsonville and Coupeville pass. Nothing else ends that way.
  3. Step 3 · contains. A % on both sides, '%land%', finds land anywhere. In Portland and Ashland the trailing % covers nothing at all, and that is allowed: % means zero or more characters.
  4. Step 4 · exactly one character. Each _ stands for exactly one character (purple). 'S____' is an S plus four more: five letters in total. Salem and Sandy fit. Seattle starts with S but has seven letters, so it fails.
  5. Step 5 · choosing a wildcard. Use % when the length does not matter and _ when it does. With no wildcard, LIKE is an equality test. In SQLite, LIKE ignores upper and lower case for plain letters; other databases may not, so check before you rely on it.

Filtering 8: Find counties whose etymology mentions a president

Scenario: For a history article, search each county’s etymology text for the word “President”; show each county with its state and etymology.

Filtering 9: Find towns ending in “ville”

Scenario: A linguistics researcher is studying town naming conventions. List each matching town with its state.

Filtering 10: Find towns with “Port” in their name

Scenario: Identify coastal or river port towns for a maritime commerce study. Show town, state, and population_2020_census.

Filtering 11: Pull the stat sheet for the four “big city” counties

Scenario: Your editor is writing a feature on the counties anchoring the Pacific Northwest’s biggest cities — King (Seattle), Pierce (Tacoma), Multnomah (Portland), and Clark (Vancouver). Report each county with its state and 2022 population, largest first. An IN list matches all four names exactly in one condition — far cleaner than chaining four ORs.

The last two examples filter on missing values. NULL does not behave like other values in a comparison, and the mistake produces an empty result instead of an error. Scroll through four tests on the same column.

NULL is not a value: = NULL versus IS NULL

-- keeps nothing
 WHERE secondary_county = NULL
-- the test that works
 WHERE secondary_county IS NULL

Scroll to build the diagram, one step at a time.

townsecondary_countyPortlandWashingtonSalemPolkEugeneNULLBendNULLTualatinWashingtonAstoriaNULLsecondary_county = NULLtest resultunknownunknownunknownunknownunknownunknownWHEREdropdropdropdropdropdrop0 rows keptsecondary_county IS NULLtest resultFALSEFALSETRUETRUEFALSETRUEWHEREdropdropkeepkeepdropkeep3 rows keptsecondary_county IS NOT NULLtest resultTRUETRUEFALSEFALSETRUEFALSEWHEREkeepkeepdropdropkeepdrop3 rows keptsecondary_county <> 'Polk'test resultTRUEFALSEunknownunknownTRUEunknownWHEREkeepdropdropdropkeepdrop2 rows kept
Step 1 · NULL means no value NULL (the dark cells) is not zero and not an empty string. It marks a value that was never recorded. Three of these six towns have no secondary county.
Step 2 · = NULL keeps nothing Comparing anything with NULL gives unknown, never true, and that holds even on the rows that are NULL. WHERE keeps a row only when its test is true, so you get zero rows and no error message.
Step 3 · IS NULL IS NULL is the test built for this. It answers plain TRUE or FALSE on every row, and the three towns with no secondary county are kept.
Step 4 · IS NOT NULL IS NOT NULL is the mirror image: it keeps the three towns that do cross into a second county.
Step 5 · the quiet trap <> 'Polk' reads like "everything except Polk", but the NULL rows test unknown and are dropped too. Two rows survive, not five. Add OR secondary_county IS NULL when you want them back.
Text version of this diagram
  1. Step 1 · NULL means no value. NULL (the dark cells) is not zero and not an empty string. It marks a value that was never recorded. Three of these six towns have no secondary county.
  2. Step 2 · = NULL keeps nothing. Comparing anything with NULL gives unknown, never true, and that holds even on the rows that are NULL. WHERE keeps a row only when its test is true, so you get zero rows and no error message.
  3. Step 3 · IS NULL. IS NULL is the test built for this. It answers plain TRUE or FALSE on every row, and the three towns with no secondary county are kept.
  4. Step 4 · IS NOT NULL. IS NOT NULL is the mirror image: it keeps the three towns that do cross into a second county.
  5. Step 5 · the quiet trap. <> 'Polk' reads like "everything except Polk", but the NULL rows test unknown and are dropped too. Two rows survive, not five. Add OR secondary_county IS NULL when you want them back.

Filtering 12: Find towns that span multiple counties

Scenario: Administrative complexity - which towns cross county boundaries? Show each town with its state, primary_county, secondary_county, and tertiary_county.

Filtering 13: Find towns entirely within one county

Scenario: The opposite query - towns that don’t span county lines. Show each town with its state and primary_county, sorted by population even though population itself isn’t selected.