Chapter 06 · Functions, NULLs, and Conditional Logic
Building Clean Derived Columns
Turn raw columns into stable analytical fields whose meaning remains clear to applications, reports, and future maintainers.
Learning outcomes
A derived column is calculated by the query rather than stored directly in the source table. A clean derived column has a stable meaning, name, type, unit, NULL policy, and calculation order.
Design aliases that describe business meaning rather than implementation mechanics.
Control calculation order, NULL behavior, and rounding.
Layer complex expressions so each stage can be verified.
Build a complete reporting projection with an explicit output contract.
A derived column is an interface
A SELECT result is an interface between the database and its consumers. Derived columns should be designed as deliberately as stored columns.
Name
Use a stable alias such as net_amount, not expr1 or total2.
Unit
State whether a value is currency, percentage, count, seconds, or text.
Type
Return a predictable type suitable for downstream consumers.
Missingness
Define what NULL, zero, empty text, and fallback labels mean.
Start with an explicit calculation contract
| Field | Definition | Type/format | NULL policy |
|---|---|---|---|
gross_amount | unit price multiplied by quantity | numeric, two display decimals | Never NULL for valid source rows |
effective_discount_rate | recorded rate; missing treated as zero for this report | numeric ratio 0–1 | Output never NULL |
discount_amount | gross amount multiplied by effective rate | numeric currency | Output never NULL |
net_amount | gross amount minus discount amount | numeric currency | Output never NULL |
sold_date | calendar date from UTC timestamp text | YYYY-MM-DD text in SQLite | Invalid source should be detected |
stock_status | availability label from stock quantity | controlled text label | Output never NULL |
Use descriptive aliases
SELECT s.sale_id, p.product_name, p.unit_price * s.quantity AS gross_amount, COALESCE(s.discount_rate, 0) AS effective_discount_rate, p.unit_price * s.quantity * COALESCE(s.discount_rate, 0) AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;Aliases should survive a code review without requiring readers to reconstruct the expression. Avoid reusing a source-column name for a value whose semantics changed.
Do not rely on a select-list alias in another select-list expression
SELECT p.unit_price * s.quantity AS gross_amount, gross_amount * COALESCE(s.discount_rate, 0) AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_id;Most engines do not make one select-list alias available to a sibling expression. Layer the calculation in a subquery.
SELECT sale_id, gross_amount, effective_discount_rate, gross_amount * effective_discount_rate AS discount_amountFROM ( SELECT s.sale_id, p.unit_price * s.quantity AS gross_amount, COALESCE(s.discount_rate, 0) AS effective_discount_rate FROM sale AS s JOIN product AS p ON p.product_id = s.product_id) AS priced_saleORDER BY sale_id;Round at the presentation boundary
SELECT sale_id, ROUND(gross_amount, 2) AS gross_amount, ROUND(discount_amount, 2) AS discount_amount, ROUND(gross_amount - discount_amount, 2) AS net_amountFROM ( SELECT s.sale_id, p.unit_price * s.quantity AS gross_amount, p.unit_price * s.quantity * COALESCE(s.discount_rate, 0) AS discount_amount FROM sale AS s JOIN product AS p ON p.product_id = s.product_id) AS calculated_saleORDER BY sale_id;Rounding each intermediate factor can produce a different total from calculating at full available precision and rounding the final monetary fields. The correct boundary is a business decision.
Expose assumptions rather than hiding them
SELECT sale_id, COALESCE(discount_rate, 0) AS effective_discount_rate, discount_rate IS NULL AS discount_was_missingFROM saleORDER BY sale_id;A consumer can use the effective rate while still distinguishing a recorded zero from a missing source value.
Keep units consistent
SELECT sale_id, COALESCE(discount_rate, 0) AS discount_ratio, ROUND(COALESCE(discount_rate, 0) * 100, 1) AS discount_percentFROM saleORDER BY sale_id;Name the unit in the alias when confusion is possible. A value of 0.15 as a ratio and 15.0 as a percentage are not interchangeable.
Derived labels should use controlled categories
SELECT product_id, product_name, stock_qty, CASE WHEN stock_qty = 0 THEN 'out_of_stock' WHEN stock_qty < 25 THEN 'low_stock' ELSE 'available' END AS stock_statusFROM productORDER BY product_id;Machine-facing labels are often safer as stable codes such as low_stock. User-facing translation and wording can occur in the presentation layer.
Build one clean reporting projection
SELECT sale_id, sold_date, customer_name, product_label, sales_channel, quantity, gross_amount, discount_percent, discount_amount, net_amount, discount_was_missingFROM ( SELECT s.sale_id, DATE(s.sold_at) AS sold_date, TRIM(c.full_name) AS customer_name, UPPER(p.category) || ': ' || p.product_name AS product_label, s.sales_channel, s.quantity, ROUND(p.unit_price * s.quantity, 2) AS gross_amount, ROUND(COALESCE(s.discount_rate, 0) * 100, 1) AS discount_percent, ROUND( p.unit_price * s.quantity * COALESCE(s.discount_rate, 0), 2 ) AS discount_amount, ROUND( p.unit_price * s.quantity * (1 - COALESCE(s.discount_rate, 0)), 2 ) AS net_amount, s.discount_rate IS NULL AS discount_was_missing FROM sale AS s JOIN customer AS c ON c.customer_id = s.customer_id JOIN product AS p ON p.product_id = s.product_id) AS sale_reportORDER BY sold_date, sale_id;The outer query declares the published column order. The inner query groups transformations and calculations into one reviewable layer.
Verify values and types
SELECT sale_id, net_amount, TYPEOF(net_amount) AS net_amount_type, discount_was_missing, TYPEOF(discount_was_missing) AS flag_typeFROM ( SELECT s.sale_id, ROUND( p.unit_price * s.quantity * (1 - COALESCE(s.discount_rate, 0)), 2 ) AS net_amount, s.discount_rate IS NULL AS discount_was_missing FROM sale AS s JOIN product AS p ON p.product_id = s.product_id) AS checked_reportORDER BY sale_id;Automated tests should verify representative values, NULL cases, boundary values, result types, and column names. SQLite boolean expressions return integer 0 or 1; other engines may return a boolean type.
Derived columns versus stored generated columns
| Choice | Use when | Tradeoff |
|---|---|---|
| Query-time expression | The value is contextual or used by one query | Repeated calculation and possible duplicated logic. |
| View | Many consumers need one governed projection | Adds a database object and versioning responsibility. |
| Generated/computed column | The expression is stable and engine supports it | Portability and write/storage behavior vary. |
| Stored ordinary column | Value must be preserved historically or externally supplied | Requires update rules to prevent drift. |
| Application calculation | Rule belongs to interaction or domain service | Database-side reports may need the same logic separately. |
Practice lab
- Create clean customer name and email columns.
- Create gross, discount, and net amounts.
- Expose both the effective discount rate and a missingness flag.
- Create stable product availability labels.
- Wrap the final report in a subquery and publish an explicit output order.
SELECT sale_id, sold_date, customer_name, product_name, gross_amount, discount_amount, net_amount, discount_was_missingFROM ( SELECT s.sale_id, DATE(s.sold_at) AS sold_date, TRIM(c.full_name) AS customer_name, p.product_name, ROUND(p.unit_price * s.quantity, 2) AS gross_amount, ROUND(p.unit_price * s.quantity * COALESCE(s.discount_rate, 0), 2) AS discount_amount, ROUND(p.unit_price * s.quantity * (1 - COALESCE(s.discount_rate, 0)), 2) AS net_amount, s.discount_rate IS NULL AS discount_was_missing FROM sale AS s JOIN customer AS c ON c.customer_id = s.customer_id JOIN product AS p ON p.product_id = s.product_id) AS report_rowsORDER BY sold_date, sale_id;Review checklist
Before publishing a derived column
- Does the alias state the field meaning and unit?
- Are NULL, empty, and zero states defined?
- Are all CASE branches type-compatible?
- Is rounding performed at the correct boundary?
- Can each calculation layer be tested independently?
- Will the same rule be duplicated elsewhere?
- Are result column names and order treated as an interface?
Common failures
Opaque aliases
Names such as calc1 force every consumer to rediscover the expression.
Same-level alias reuse
This is unsupported or behaves differently across engines.
Mixed units
Ratios, percentages, currency, and counts need explicit names.
Silent NULL replacement
A usable fallback can erase source-state information.
Premature rounding
Rounding intermediate values can change totals.
Copying the same business expression
Duplicate definitions drift; govern reusable logic deliberately.
Chapter 6 summary
- Scalar functions transform string, numeric, date/time, and typed values.
COALESCEandNULLIFprovide explicit missing-value policies.CASEcreates conditional values according to ordered rules.- Function portability requires testing names, syntax, types, and behavior.
- Derived columns are output contracts with names, units, types, NULL rules, and calculation boundaries.
Chapter 7 introduces joins and explains how rows from related tables are matched, preserved, multiplied, and debugged.