PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

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_idbalanceactive_cardsCASE Evaluation BranchtierNULLIF(active_cards, 0)balance_per_card
act_011500003WHEN balance >= 100000PLATINUM350000.00
act_02400000WHEN balance >= 25000GOLDNULL (safe zero guard)NULL (No error)
act_0350001ELSE branchSTANDARD15000.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

Problematic Syntax (Anti-Pattern)SELECT * FROM users WHERE strftime('%Y', created_at) = '2026';
Optimized AlternativeSELECT * 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

Problematic Syntax (Anti-Pattern)SELECT numerator / denominator FROM metrics;
Optimized AlternativeSELECT 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

Problematic Syntax (Anti-Pattern)SELECT IFNULL(display_name, 'Anonymous') FROM profiles;
Optimized AlternativeSELECT 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
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
EmployeeEmpID, Name, DeptID, Salary, ManagerID8 rowsStaff directory with salaries and reporting structure
DepartmentDeptID, DeptName, Budget8 rowsOrganizational departments and annual budgets
ProjectProjectID, ProjectName, DeptID, Deadline5 rowsActive enterprise engineering projects
Interactive Query Workspace

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.

Explore Other SQL Practice Tracks