skip to content

In SQL, why can COUNT(first_name || ' ' || middle_name) come in below the customer row count, and does CONCAT fix that on every engine?

level: middleimportance: must knowfreq 58%

answer

  1. one unknown part, one unknown whole
  2. what COUNT of an expression skips
  3. operator and function can disagree
  4. CONCAT's NULL rule varies by engine
  5. MySQL's default reading of ||

basics

~20 s

With standard ||, one NULL operand makes the whole expression NULL, and COUNT skips NULL, so rows with no middle name drop out. CONCAT is no universal fix: PostgreSQL and SQL Server ignore NULLs, but MySQL's CONCAT returns NULL.

solid answer

~50 s

On PostgreSQL and SQLite, `||` propagates NULL: any NULL operand makes the result NULL, so `first_name || ' ' || middle_name` is NULL for every customer without a middle name. `COUNT(expression)` counts only non-NULL values, which explains the gap in the report, and the same NULL drops rows from a filter or a join on the built value. `CONCAT` is not a portable fix. PostgreSQL and SQL Server ignore NULL arguments, MySQL returns NULL if any argument is NULL, and Oracle's `||` and two-argument `CONCAT` both treat NULL as empty, so the symptom never appears there. MySQL also reads `||` as logical OR by default, unless `PIPES_AS_CONCAT` is set. For names I prefer `CONCAT_WS(' ', first_name, middle_name, last_name)`: it is widely available, skips NULLs and leaves no double space where the middle name was missing.

go deeper

for a junior

Know that one NULL part can make a whole concatenation NULL and that COUNT of an expression skips NULL results. Then remember that CONCAT is a different function with its own rule.

for a middle

State the engine differences exactly: which ignore NULLs in CONCAT, which propagate through the operator, and why MySQL's default || is not concatenation at all. Add the double-space effect and CONCAT_WS.

for a senior

Trace a drifting report to its cause by comparing COUNT(*) with COUNT of the built value and of each part. Say how a blank from all-NULL CONCAT hides missing data from an IS NULL test.

for a principal

If reports across a team rebuild names by hand, each one picks its own NULL rule and the totals disagree. The lead's angle is agreeing one definition of a display name and where it is computed.

## Why the expression turns NULL **NULL** in SQL means a missing or unknown value, and an operator applied to an unknown value generally gives an unknown result. String concatenation with `||` follows that rule on engines that implement it as the standard describes. So for a customer with `first_name = 'Ann'` and `middle_name` NULL: ```sql SELECT 'Ann' || ' ' || NULL; -- NULL on PostgreSQL and SQLite ``` The whole value is lost, not just the missing part. The literal space and the known first name do not survive. ## Why the report's row count changes The row itself is still in the table, so `COUNT(*)` does not move. What changes is anything that consumes the built value: - **`COUNT(expression)`** counts only non-NULL results, so it falls below `COUNT(*)` by exactly the number of rows where a part was NULL. - **`WHERE full_name = 'Ann Lee'`** cannot be true for NULL, so the row is filtered out; `<>` also fails to keep it. - **A join on a built key** finds no match, so an inner join loses the row and an outer join shows it unmatched. - **`GROUP BY full_name`** gathers all the NULL results into one group, merging unrelated customers. That is why this bug shows up as a report whose totals drift rather than as an error. Nothing fails, and the rows with a middle name look perfectly correct. ## What each engine does The operator and the function are separate decisions, and engines made different ones: | Engine | `'a' \|\| NULL` | `CONCAT('a', NULL)` | |---|---|---| | PostgreSQL | NULL | `'a'` | | SQLite | NULL | `'a'` (in recent versions) | | SQL Server | Classic operator is `+`; `'a' + NULL` is NULL by default | `'a'` | | MySQL, default mode | `\|\|` is logical OR, not concatenation | NULL | | Oracle | `'a'` | `'a'` (two arguments only) | Three of these deserve a sentence each: 1. **MySQL** reads `||` as logical OR unless the `PIPES_AS_CONCAT` SQL mode is on, so a query written for another engine runs and returns numbers instead of text. 2. **Oracle** treats an empty string and NULL as the same thing, so concatenating a NULL simply adds nothing. The original symptom does not occur there, and moving a query off Oracle can introduce it. 3. **`CONCAT` on PostgreSQL and SQL Server** returns an empty string when every argument is NULL, not NULL. A test for `full_name IS NULL` then finds nothing, and a customer with no name data counts as present. ## The double-space problem Switching to a NULL-ignoring `CONCAT` fixes the count and creates a smaller bug. Separators are non-NULL literals, so they stay: ```sql SELECT CONCAT('Ann', ' ', NULL, ' ', 'Lee'); -- 'Ann Lee' on PostgreSQL ``` That double space looks harmless until the value is compared with `'Ann Lee'` from another system, and then a join quietly loses the row again. `CONCAT_WS`, concatenate with separator, solves both problems at once, because it adds the separator only between non-NULL arguments: ```sql SELECT CONCAT_WS(' ', 'Ann', NULL, 'Lee'); -- 'Ann Lee' ``` `CONCAT_WS` is widely available, but check your engine and version before relying on it. ## Choosing a fix - If the engine has it, use `CONCAT_WS` for anything built from optional parts. - If not, replace the missing part explicitly with `COALESCE`, moving the separator inside the optional part so it disappears too: `first_name || COALESCE(' ' || middle_name, '') || ' ' || last_name`. On a NULL-propagating engine the inner expression is NULL when `middle_name` is. - On Oracle the count needs no fix, but the double space remains unless the separator travels with the optional part. - Keep required parts visible. If a NULL `last_name` is a data defect, a function that hides it also hides the defect, so count those rows separately. ## Measuring the damage first Before changing a report, measure how many rows the expression affects. Comparing the two counts in one statement tells you whether the discrepancy you were asked about is this one or something else: ```sql SELECT COUNT(*) AS all_rows, COUNT(first_name || ' ' || middle_name) AS built_names, COUNT(middle_name) AS with_middle_name FROM customer_import; ``` On a NULL-propagating engine, if `built_names` is below `with_middle_name`, then some first names are NULL too, and the problem is wider than optional middle names.

  • Why does CONCAT(first_name, ' ', middle_name, ' ', last_name) still look wrong on an engine that ignores NULLs?
    The separators are non-NULL literals, so they stay when the middle name is skipped, producing 'Ann Lee' with two spaces. The count is fixed, but the value no longer equals 'Ann Lee' from another system, so a later comparison or join fails quietly. CONCAT_WS adds a separator only between non-NULL arguments and avoids this.
  • On an engine where CONCAT ignores NULLs, what does it return when every argument is NULL?
    PostgreSQL and SQL Server return an empty string, not NULL. A test for full_name IS NULL then finds nothing, and a customer with no name data shows as a blank value and still counts. On MySQL the same call returns NULL, so check the source columns rather than the built value when you need to find missing names.
  • How do you measure how many rows a NULL middle name affects before changing the report?
    Put the counts in one statement: COUNT(*), COUNT of the built expression, and COUNT(middle_name). On a NULL-propagating engine the gap between the first two is the affected rows. If the built count is below COUNT(middle_name), another part is NULL as well, so the problem is wider than optional middle names.

  • CONCAT always ignores NULL arguments, so switching to it fixes the report on any engine.
  • COUNT(expression) counts every row, whether or not the expression turned out to be NULL.
  • The || operator concatenates strings on MySQL with its default settings, just as elsewhere.
  • Concatenating a NULL treats it as an empty string on all engines.
  • A CONCAT with literal separators removes the extra space when the middle name is NULL.