Brightlane's engineering team is documenting the difference in missing-input behavior between the || operator and the CONCAT function.
Write a query to return two values in a single row: the output of concatenating 'Customer: ' and a SQL NULL using the || operator, and the output of passing those same values to CONCAT.
Output:
- A single row with columns
pipe_result(the||output, which will be missing) andconcat_result(theCONCAToutput, which will be the prefix'Customer: 'alone).
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
'Customer: ' || NULL AS pipe_result,
CONCAT('Customer: ', NULL) AS concat_resultThe shape
The two columns surface the exact behavioral split the engineering team is documenting: || returns NULL when either operand is NULL, and CONCAT returns the present value alone, treating the NULL as an empty string. Running both expressions in one row puts the contrast on a single line of output.
Clause by clause
SELECT 'Customer: ' || NULL AS pipe_resultevaluates the||concatenation on a known prefix and a NULL right operand.||follows the universal NULL-propagation rule: a NULL anywhere in the expression makes the whole result NULL. The prefix is not preserved;pipe_resultcomes back as a missing value., CONCAT('Customer: ', NULL) AS concat_resultevaluates the same two values throughCONCATinstead.CONCATwalks its argument list and treats each NULL as an empty string, so the prefix is concatenated against nothing andconcat_resultcomes back as the string'Customer: '. There is noFROMbecause both expressions are evaluated directly from the literals supplied in the query.
Why this and not running each form in a separate query
The whole point of the documentation row is the side-by-side contrast. A learner reading the result table sees pipe_result = NULL and concat_result = 'Customer: ' on the same line, against the same inputs, and the difference becomes a single mental snapshot. Splitting them across two queries forces the reader to context-switch between two result panels and reconstruct the comparison.
The trap
The contrast looks like a stylistic choice but is a semantics decision the rest of the codebase depends on. If a display label needs to vanish entirely when any input is missing (so a downstream consumer can detect the gap), || is the right tool. If a display label should keep showing whatever pieces are present (so the user still sees something), CONCAT is. Pick the wrong one and the report either silently drops rows or silently shows incomplete labels as if they were complete, with no error path to alert anyone. The choice is not interchangeable on real data.
You practiced contrasting || against CONCAT(...) on a missing input — || propagates the missing value through, CONCAT treats it as an empty string; the choice between them is a NULL-semantics decision.
