Built-in Functions & CASE WHEN
Manipulate text, math, and dates with built-in functions, and implement conditional log mappings.
Relational Theory & Optimization Architecture
Scalar functions and conditional expressions transform individual data points per row. These include CASE WHEN branching, string manipulation, date arithmetic, and NULL handling via COALESCE and NULLIF. Data engineers rely on scalar logic to normalize disparate data feeds, enforce data-cleaning rules, and compute derived attributes for reporting schemas.
Physical Storage & Buffer Pool Mechanics
Scalar functions operate on a row-by-row basis in memory after row retrieval. However, applying scalar functions to indexed columns inside a WHERE clause (e.g. WHERE UPPER(last_name) = 'SMITH') destroys the SARGability (Search Argument Ability) of the predicate. When an expression wraps a column, the B-Tree index cannot be traversed via binary search; the database engine is forced to scan every record and evaluate the function repeatedly.
Per-Row Expression Evaluation and Short-Circuiting
CASE expressions evaluate sequentially from top to bottom. The first WHEN clause whose boolean expression evaluates to TRUE returns the corresponding THEN expression, immediately terminating further branch evaluation. If no WHEN branch evaluates to TRUE, the ELSE expression is returned. If ELSE is omitted and no conditions match, the CASE expression evaluates to NULL.
Worked Query Walkthrough: Customer Tiering with Safe Ratio Calculation
SELECT account_id,
balance,
CASE
WHEN balance >= 100000 THEN 'PLATINUM'
WHEN balance >= 25000 THEN 'GOLD'
ELSE 'STANDARD'
END AS tier,
ROUND(balance / NULLIF(active_cards, 0), 2) AS balance_per_card
FROM bank_accounts;Step-by-Step Row Evaluation Matrix
| account_id | balance | active_cards | CASE Evaluation Branch | tier | NULLIF(active_cards, 0) | balance_per_card |
|---|---|---|---|---|---|---|
| act_01 | 150000 | 3 | WHEN balance >= 100000 | PLATINUM | 3 | 50000.00 |
| act_02 | 40000 | 0 | WHEN balance >= 25000 | GOLD | NULL (safe zero guard) | NULL (No error) |
| act_03 | 5000 | 1 | ELSE branch | STANDARD | 1 | 5000.00 |
Runtime Trace Analysis
For account 02, dividing balance by active_cards would cause a fatal 'division by zero' crash. By wrapping active_cards with NULLIF(active_cards, 0), zero is transformed into NULL. Any number divided by NULL safely evaluates to NULL, protecting pipeline execution.
Common Traps, Anti-Patterns & Debugging Solutions
1. Non-SARGable Function Wrapping in Predicates
SELECT * FROM users WHERE strftime('%Y', created_at) = '2026';SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';Wrapping created_at in strftime() prevents index seeking. Rewriting the predicate as a half-open range allows the optimizer to execute an efficient B-Tree range seek.
2. Unprotected Division by Zero
SELECT numerator / denominator FROM metrics;SELECT numerator / NULLIF(denominator, 0) FROM metrics;If any row contains a denominator of zero, standard SQL engines abort query execution with an exception. NULLIF converts zero to NULL, producing a graceful NULL result.
3. COALESCE vs IFNULL Portability Trap
SELECT IFNULL(display_name, 'Anonymous') FROM profiles;SELECT COALESCE(display_name, username, 'Anonymous') FROM profiles;IFNULL is non-standard and limited to two arguments. COALESCE is standard ANSI SQL, accepts arbitrary argument counts, and evaluates sequentially until the first non-NULL value is found.
Production Strategy & Query Engineering Best Practices
When working with real-world datasets containing NULLs and zeros, always use NULLIF for defensive division to avoid ZeroDivision errors, and design SARGable date-range and string search queries so database indexes can be utilized.
Interactive WebAssembly SQL Playground
Client-Side SQLite Engine| Table | Key Columns | Sample Row Count | Description |
|---|---|---|---|
| Employee | EmpID, Name, DeptID, Salary, ManagerID | 8 rows | Staff directory with salaries and reporting structure |
| Department | DeptID, DeptName, Budget | 8 rows | Organizational departments and annual budgets |
| Project | ProjectID, ProjectName, DeptID, Deadline | 5 rows | Active enterprise engineering projects |
Run custom SQL queries against the Employee, Department, Project schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.
Frequently Asked Questions on Built-in Functions & CASE WHEN
How does NULLIF differ from COALESCE in SQL?
NULLIF takes two arguments and returns NULL if they are equal; otherwise, it returns the first argument. COALESCE takes multiple arguments and returns the first non-NULL argument in the list.
What does SARGable mean in SQL query optimization?
SARGable stands for 'Search Argument Able'. A predicate is SARGable if the database engine can utilize an index seek to satisfy it, which requires avoiding function calls or mathematical operations on indexed columns.
Do CASE statements evaluate all branches or short-circuit?
ANSI SQL specifies that CASE expressions evaluate conditions sequentially and return when the first matching WHEN condition is found. However, database optimizers may evaluate expressions out of order if no side effects exist.
How do string concatenations handle NULL values?
In ANSI SQL and SQLite, concatenating any string with NULL using || results in NULL. In Postgres and SQL Server, CONCAT(val1, val2) automatically treats NULL as an empty string.