Why it happens
In standard SQL and SQLite, the string concatenation operator || propagates NULL: if any operand is NULL, the entire expression evaluates to NULL ('Alan' || ' ' || NULL || ' ' || 'Turing' -> NULL).
---
Fix 1: Wrap the optional middle name with COALESCE
Append the leading space inside the || only when middle is present, falling back to '' when it is NULL:
SELECT
id,
first || COALESCE(' ' || middle, '') || ' ' || last AS full_name
FROM people
ORDER BY id;
Why COALESCE(' ' || middle, '') works so cleanly:
- For Ada (middle = 'King'): ' ' || 'King' is ' King', so 'Ada' || ' King' || ' ' || 'Lovelace' -> 'Ada King Lovelace'.
- For Alan (middle = NULL): ' ' || NULL is NULL, which COALESCE replaces with '', so 'Alan' || '' || ' ' || 'Turing' -> 'Alan Turing' (with no double space!).
Fix 2: Use concat_ws (SQLite 3.44.0+)
Since you're on SQLite 3.53.0, concat_ws(separator, ...) automatically skips NULL arguments:
SELECT
id,
concat_ws(' ', first, middle, last) AS full_name
FROM people
ORDER BY id;
Both return 'Ada King Lovelace' and 'Alan Turing'.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: 'Ada King Lovelace' and 'Alan Turing'.
SQL block 1 (SELECT id,): ✅ matches the expected result
| id | full_name |
|---|---|
| 1 | Ada King Lovelace |
| 2 | Alan Turing |
SQL block 2 (SELECT id,): ✅ matches the expected result
| id | full_name |
|---|---|
| 1 | Ada King Lovelace |
| 2 | Alan Turing |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.