Why is the whole name NULL if only last_name is NULL?

In Postgres, `first_name || ' ' || last_name` is NULL if any operand is NULL — same rule as NULL + 5. SQL Server's `+` for strings behaves the same unless CONCAT_NULL_YIELDS_NULL is off (don't rely on that).

Does MySQL do this too?

MySQL CONCAT() skips NULL arguments and still returns the other parts. CONCAT(first, ' ', last) with last NULL is 'Ada ' not NULL. That's why a query tested on MySQL 'works' and goes blank on Postgres.

What's the portable fix?

COALESCE each piece to '': COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''). Or CONCAT_WS(' ', first_name, last_name) on Postgres/MySQL, which skips NULLs and doesn't leave a double space as badly. Then TRIM.

Is this the same as SUM returning NULL?

Same NULL-poisoning family, different operator. SUM over zero rows is NULL; concat with one NULL column blanks a label you'd still want to show.

Does this affect filters, not just display?

Yes. WHERE first_name || last_name = 'AdaLovelace' misses every row with a NULL side. Search and 'full name' filters need the same COALESCE/CONCAT_WS treatment.

SQL Concat NULL Swallows String | || vs CONCAT

You ask for a CSV of customers: full name, email. Half the name column is blank. Those people have a first name in the table. last_name is NULL, and in Postgres that is enough to erase the whole concatenation.

NULL does not mean "skip this part"

-- Postgres: if last_name is NULL, the entire expression is NULL
SELECT first_name || ' ' || last_name AS full_name FROM customers;

-- SQL Server: same with +
SELECT first_name + ' ' + last_name FROM customers;

'Ada' || ' ' || NULL is NULL, not 'Ada '. That is the same three-valued rule as NULL + 5: unknown poisons the expression.

MySQL's CONCAT() is the exception people remember from the wrong database:

-- MySQL: NULLs are skipped, you still get 'Ada '
SELECT CONCAT(first_name, ' ', last_name) FROM customers;

So a prompt that "worked" against MySQL goes blank on Postgres or SQL Server with no syntax error — another same SQL, different database footgun.

The fix

Make each piece non-NULL before you glue:

-- works everywhere
SELECT TRIM(
  COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')
) AS full_name
FROM customers;

Postgres and MySQL also have CONCAT_WS (concat with separator), which skips NULL parts so you don't get a dangling space from a missing middle name:

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM customers;

SQL Server has CONCAT() too (2012+), which likewise treats NULL as ''. The dangerous operators are Postgres || and SQL Server +.

How to catch it

If a "name" or "label" column in the result is NULL more often than the source columns:

How many rows have first_name IS NOT NULL?
How many have full_name IS NULL in your SELECT?
If those differ, you're concatenating NULLs. Show the expression.

Same check for city || ', ' || country, invoice line descriptions, and any filter on the concatenated string.

The prompt that heads this off

When concatenating text (names, addresses, labels), don't use
Postgres || or SQL Server + on raw columns. COALESCE each part to ''
or use CONCAT / CONCAT_WS. Say which database you're on. If a display
column is NULL, check whether one of the inputs was NULL before
assuming the row is empty.

The short version

One missing last name should not delete the first name. In Postgres and SQL Server, || / + will do exactly that. COALESCE or CONCAT_WS is the version that still prints "Ada."

Read-only MCP gateway: mcpserver.design.

Related reading: