Brightlane is running a regional account reassignment that moves every customer outside the New York territory to a different sales team.
Write a query to return the ID and name of every customer being reassigned.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - A customer is being reassigned if their
cityis any value other than'New York', or if theircityisNULL(no city on record). - Some customers have
cityset toNULL; those customers must be included in the reassignment.
Output:
- One row per reassigned customer, with columns
idandname.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
name
FROM
customers
WHERE
city <> 'New York'
OR city IS NULLThe shape
The inequality alone won't reach the NULL-city customers, so an explicit OR city IS NULL is bolted on. Both branches together cover everyone who isn't in New York — including the customers with no city on record.
Clause by clause
SELECT id, namereturns the two columns the reassignment process needs.FROM customersreads the customer table.WHERE city <> 'New York' OR city IS NULLis the compound filter.city <> 'New York'keeps any row whose city is a recorded value other than'New York'.city IS NULLkeeps rows that have no city on record at all. TheORaccepts a row when at least one side is true, so customers inChicago,Toronto,London, and any other city pass on the first condition, and customers withNULLcity pass on the second.
Why the OR city IS NULL is required
The inequality city <> 'New York' only returns true when PostgreSQL can definitively determine that the value is something other than 'New York'. For a row where city is NULL, the comparison evaluates to unknown — PostgreSQL can't say whether a missing value is different from 'New York', because it doesn't know what the missing value is. WHERE keeps only rows where the condition is true, so every unknown row is silently dropped, and the NULL-city customers never make it into the reassignment list.
OR city IS NULL adds a second branch that explicitly captures the NULL rows. IS NULL always returns true or false, never unknown, so the rows with no city on file pass through this branch and land in the result.
The trap
Inequalities silently exclude NULL rows. <>, !=, >, <, >=, <= — every comparison operator returns unknown when one side is NULL, and WHERE drops unknown along with false. So a query that reads as "every customer outside New York" ends up meaning "every customer whose city is a recorded value other than New York," and the customers with no city on file disappear from the result without a sound. Whenever an inequality test is meant to include the missing-value rows, the fix is an explicit OR <column> IS NULL — the inequality alone can't see them.
You practiced handling NULL alongside an inequality by adding an explicit OR ... IS NULL. The recurring shape any time NULL rows would otherwise be excluded by an inequality test.
