Brightlane's data quality audit is testing how the || operator behaves when one operand is missing.
Write a query to return the result of concatenating 'Order-' with a SQL NULL using the || operator.
Output:
- A single row with one column,
order_ref, containing the result. The result will itself be missing because the right operand is missing.
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
'Order-' || NULL AS order_refThe shape
'Order-' || NULL returns NULL, not the partial string 'Order-'. The || operator follows the universal NULL-propagation rule: any operand that is NULL poisons the entire expression, and the result comes back as a missing value.
Clause by clause
SELECT 'Order-' || NULL AS order_refevaluates the concatenation and labels the resulting columnorder_ref. The left operand is a real string, the right operand isNULL, and||returns NULL the moment either side is NULL. The prefix is not preserved; the whole result is missing. There is noFROMbecause both operands are supplied directly in the query.
Why this and not CONCAT('Order-', NULL)
CONCAT would return the string 'Order-' here, because CONCAT treats a NULL argument as if it were an empty string and skips it. || does not. The two functions encode different intents about what a missing input means: || says a missing input invalidates the whole result, CONCAT says a missing input is omitted from the result. The audit's job is to verify the || semantics, so the operator is the correct tool here even though it returns NULL.
The trap
A NULL output from || is silent. There is no error, no warning, just an empty cell where a string was expected. In a report column built by concatenating several fields, one missing field rewrites the entire label to NULL. When the inputs might be NULL and a partial label is acceptable, || is the wrong tool. Reach for CONCAT or CONCAT_WS so the present values still come through.
You practiced || propagating a missing value — the operator follows the universal NULL-in-NULL-out rule, so a single missing operand poisons the entire concatenation chain.
