QuickDevGuide

Home › SQL Database

CASE, NULL Handling & Common Functions — SQL Database

CASE expressions CASE WHEN condition THEN value … ELSE value END adds if/else logic anywhere an expression is allowed: SELECT, ORDER BY, GROUP BY, even inside aggregates. NULL semantics NULL means unknown. Any comparison with NULL (= NULL, <> NULL) is unknown, so those rows are filtered out. Need Use Test for NULL IS NULL / IS NOT NULL First non-null value COALESCE(a, b, 'default') Turn a value into NULL NULLIF(x, 0) — avoids division by zero NULL-safe equality IS NOT DISTINCT FROM (PostgreSQL), IS (SQLite) Function cheat sheet (dialects differ!) Purpose SQLite PostgreSQL SQL Server MySQL Concatenate a b a b, CONCAT a + b, CONCAT CONCAT Substring substr SUBSTRING SUBSTRING SUBSTRING Current date date('now') CURRENTDATE GETDATE() CURDATE() Date part strftime('%Y', d) EXTRACT(YEAR FROM d)…

Key Takeaways