Fall Back from City to Country — COALESCE and NULLIF in SQL

The problem

Brightlane's marketing team needs a single location label for every customer to populate a targeting segment.

Write a query to return each customer's name and their best-available location label.

Assumptions:

The customers table has one row per customer with a name, a city, and a country.
Some customers have a missing city value, but every customer has a recorded country.
The label should prefer city when it is recorded; for a customer with a missing city, the label should fall back to their country.

Output:

One row per customer, with columns name and location.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  COALESCE(city, country) AS location
FROM
  customers

The shape

COALESCE(city, country) picks the first non-missing value from a priority list, city first and country as the fallback, and returns whichever it lands on. Every customer ends up with a single labeled location, populated either from their recorded city or from their country when the city is missing.

Clause by clause

SELECT name, COALESCE(city, country) AS location returns each customer's name and their best-available location label. COALESCE short-circuits left to right: if city is not NULL, the expression returns it and country is never consulted; only when city is NULL does it fall through to country. The alias AS location names the derived column for the targeting segment.
FROM customers reads every row in the customer table. There is no filter because every customer needs a location label.

Why this and not `CASE WHEN city IS NOT NULL THEN city ELSE country END`

Both expressions return the same value on this data, and PostgreSQL rewrites COALESCE into that exact CASE form during planning. COALESCE reads as the priority list it is: city, then country. The CASE version forces the reader to reconstruct the priority from a condition. For a straight first-non-NULL pick across two or more columns, COALESCE is the cleaner spelling.

The trap

Argument order matters. COALESCE(city, country) and COALESCE(country, city) return different labels for any customer whose city and country are both recorded. The first form prefers the city and falls back to the country; the second prefers the country every time. When the columns have a meaningful priority, as they do here, the leftmost argument is the one whose value wins when both are present.

You practiced COALESCE(a, b) over two columns — pick the first non-missing value in priority order.