Built-in Functions & CASE WHEN
Manipulate text, math, and dates with built-in functions, and implement conditional log mappings.
SQL Query Architecture & Optimization Guide
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.
Scalar functions are evaluated on each candidate row as it passes through the execution pipeline. Functions placed in WHERE predicates must be evaluated for every row unless functional indexes are present.
Wrapping indexed columns in functions (such as WHERE LOWER(email) = 'user@example.com') prevents the optimizer from performing B-Tree index seeks, forcing expensive full table scans. Use un-wrapped comparisons or create functional indexes.
Interactive WebAssembly SQL Playground
Frequently Asked Questions on Built-in Functions & CASE WHEN
What is the difference between COALESCE and NULLIF?
COALESCE returns the first non-NULL value from a list of arguments. NULLIF compares two values and returns NULL if they are equal, which is useful for preventing division by zero errors.
What does it mean for a query condition to be SARGable?
SARGable (Search Argument Able) conditions allow the query planner to leverage B-Tree index seeks instead of full table scans. Wrapping indexed columns in functions prevents SARGability.
How does Searched CASE differ from Simple CASE?
Simple CASE matches an expression against literal values (CASE col WHEN 1 THEN ...). Searched CASE evaluates independent boolean expressions (CASE WHEN col > 10 THEN ...), offering greater flexibility.