WHERE Clauses¶

Goal: Master the WHERE clause — the only SQL part you write in ArcGIS Pro selections and definition queries.

Mental model¶

WHERE answers one question, per row:

"Should this row be included? True or false?"

If true → keep it. If false → discard it. That's it.

Comparison operators¶

POP > 100000
PRICE <= 250000
STATE = 'GA'
STATE <> 'GA'

Strings¶

Strings are wrapped in single quotes:

NAME = 'Atlanta'
COUNTY = 'Fulton'

Strings are case-sensitive in some sources (file geodatabase) and not in others (SQL Server with default collation). For safety, lowercase your input first using Calculate Field, or use:

LOWER(STATE) = 'ga'

Numbers¶

No quotes for numbers:

POP > 100000
ELEV >= 1500.5

Watch out — quoted numbers are treated as strings:

POP > '100000'        -- ❌ string compare, weird results
POP > 100000          -- ✅

Parentheses (use them!)¶

AND binds tighter than OR. So this:

A = 1 AND B = 2 OR C = 3

is parsed as:

(A = 1 AND B = 2) OR C = 3

If you wanted "(A=1) AND (B=2 OR C=3)", you must write it explicitly. Always use parentheses for clarity:

(A = 1) AND (B = 2 OR C = 3)

NULLs are weird¶

NULL means "unknown". And anything compared to NULL is unknown:

notes = NULL          -- ❌ never matches
notes <> NULL         -- ❌ never matches
notes IS NULL         -- ✅ matches if null
notes IS NOT NULL     -- ✅ matches if any value

→ Detail: IS NULL.

Negation¶

NOT (state = 'GA')                       -- everything but GA
NOT (state IN ('GA','FL','SC'))          -- everything but those three
NOT (pop > 100000)                       -- pop <= 100000 (and NOT NULL!)

Watch the NULL trap: NOT (pop > 100000) excludes rows where pop is null, because pop > 100000 is unknown for nulls. To include them: NOT (pop > 100000) OR pop IS NULL.

Using fields on both sides¶

You can compare two fields:

ENROLLMENT > CAPACITY                    -- over capacity
DEATHS_2024 > DEATHS_2023                -- increased
PRICE_PER_SQFT > MEDIAN_PRICE_PER_SQFT   -- above market

Quoting fields¶

If a field name has spaces or is reserved (COUNT, DATE), quote it:

Don't use single quotes for field names — those are for string literals.

"Median Income" > 50000      -- ✅ field with space
'Median Income' > 50000      -- ❌ comparing string literal to literal

Quick examples¶

-- Cities with population in a range, in two states
(STATE = 'GA' OR STATE = 'FL') AND POP BETWEEN 50000 AND 200000

-- Schools that don't have a website
WEBSITE IS NULL OR WEBSITE = ''

-- Parcels valued above their land value
TOTAL_VALUE > LAND_VALUE

-- Not residential
NOT (LANDUSE LIKE 'Residential%')

Common mistakes¶

Practice¶

→ Next: LIKE Operator.