Brightlane's location analytics vendor will only geocode customer records that have a city on file.
Write a query to return the ID and name of every customer with a recorded city value.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - Some customer records have a recorded
cityvalue; others havecityset toNULL. - The geocoding vendor needs every customer with a non-null
cityvalue.
Output:
- One row per customer with a city on file, 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 IS NOT NULLThe shape
The geocoding vendor's hard requirement — a non-null city value — becomes the filter directly. IS NOT NULL keeps every customer whose city is on file and drops every customer whose city is missing.
Clause by clause
SELECT id, namereturns the two columns the vendor handoff needs: a stable identifier to match results back against Brightlane's records, and the customer name for the vendor's own quality checks.FROM customersreads the customer table. The handoff covers the whole customer base, so no narrower source is needed.WHERE city IS NOT NULLkeeps only rows whosecityis present.IS NOT NULLreturnstruefor the 60 customers with a recorded city andfalsefor the 9 withNULL. The gap in theidsequence in the output (20,26,30,34,41,46,52,56,66are missing) is the visible trace of the customers filtered out.
Why this and not a separate scrub step
A reasonable alternative is to send the full customer list to the vendor and let them filter out the null-city rows on their end. The vendor would charge for every row they processed (including rejections), and Brightlane would pay for the wasted work. Filtering at the source means the handoff carries only valid records.
The same principle applies any time a downstream system has a hard precondition on the data. Filtering at the query level is the contract: "every row I'm handing you meets the requirement."
The trap
WHERE city <> NULL looks like the right way to say "city is not null" and quietly returns zero rows. The handoff arrives empty, the vendor finds nothing to geocode, and the bug looks like a vendor problem until someone reads the SQL. <> is a comparison operator, and comparing anything to NULL produces unknown, never true. WHERE keeps only rows whose condition is clearly true, so the entire customer base gets filtered out.
The fix is the same shape as every other NULL trap: when the test is for presence or absence of a value, use IS NOT NULL or IS NULL. The comparison operators (=, <>, <, >) don't work against NULL, and the failure is silent.
You practiced using IS NOT NULL to scope to records ready for downstream processing. Filter-by-completeness is the recurring shape whenever a pipeline only handles fully-populated records.
