Conditional & NULL Functions Interview Questions & Answers

CASE is SQL's conditional expression — an inline if/else that returns a value. It has two forms:

Simple CASE — compares one expression to several values:

SELECT order_id,
       CASE status
         WHEN 'pending'   THEN 'Awaiting payment'
         WHEN 'shipped'   THEN 'On its way'
         WHEN 'delivered' THEN 'Complete'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

Searched CASE — evaluates independent boolean conditions:

SELECT order_id, total,
       CASE
         WHEN total >= 500 THEN 'Large'
         WHEN total >= 100 THEN 'Medium'
         ELSE 'Small'
       END AS size_category
FROM orders;

CASE can appear anywhere an expression is valid: SELECT, WHERE, ORDER BY, GROUP BY, inside aggregate functions.

Rule of thumb: use the simple form for equality checks on a single column; use the searched form when conditions involve different columns, comparisons, or IS NULL checks.

COALESCE(expr1, expr2, …) returns the first non-NULL value from its argument list. It is the standard way to substitute a default for a NULL.

-- Use a fallback when a column might be NULL
SELECT COALESCE(phone, 'N/A')          AS phone    FROM users;
SELECT COALESCE(discount, 0)           AS discount FROM orders;
SELECT COALESCE(nickname, first_name)  AS display  FROM users;

-- Chain multiple fallbacks
SELECT COALESCE(preferred_email, work_email, personal_email, 'no-email@unknown.com')
FROM contacts;

-- Avoid NULL in arithmetic (NULL + anything = NULL)
SELECT price * COALESCE(quantity, 0) AS line_total FROM cart_items;

COALESCE short-circuits: it stops evaluating arguments as soon as it finds a non-NULL value. All arguments must be type-compatible.

Rule of thumb: use COALESCE to provide defaults for nullable columns. It is cleaner and more portable than CASE WHEN col IS NULL THEN default ELSE col END — they are exactly equivalent.

NULLIF(expr1, expr2) returns NULL if the two expressions are equal, otherwise returns expr1. It is the inverse of COALESCE — converting a specific value to NULL.

-- Prevent division-by-zero: replace 0 with NULL before dividing
SELECT numerator / NULLIF(denominator, 0) AS ratio FROM metrics;
-- If denominator = 0 → NULL instead of ERROR

-- Convert a sentinel value to NULL
SELECT NULLIF(phone, 'N/A') AS phone FROM contacts;
-- 'N/A' → NULL; other values pass through unchanged

-- Combine with COALESCE for clean defaults
SELECT COALESCE(NULLIF(TRIM(notes), ''), 'No notes') AS notes FROM tickets;
-- Empty-string or whitespace-only → 'No notes'

Rule of thumb: reach for NULLIF(col, 0) any time you are dividing by a column that may be zero. Pair with COALESCE when you also want to replace the resulting NULL with a default.

These are two-argument shortcuts for replacing NULL with a default — they are equivalent to COALESCE(expr, default) but are database-specific:

Function	Database
IFNULL(expr, default)	MySQL
NVL(expr, default)	Oracle
ISNULL(expr, default)	SQL Server / Sybase
COALESCE(expr, default)	All (ANSI SQL)

-- MySQL
SELECT IFNULL(discount, 0) FROM orders;

-- SQL Server
SELECT ISNULL(discount, 0) FROM orders;

-- COALESCE — works everywhere and accepts more than two arguments
SELECT COALESCE(discount, 0) FROM orders;

Rule of thumb: use COALESCE in any code that needs to be portable. Use IFNULL/ISNULL only in database-specific stored procedures where portability is not a concern. Avoid NVL outside Oracle.

IIF(condition, true_value, false_value) is a compact inline if/else available in SQL Server and MySQL (as IF). It is syntactic sugar for a two-branch CASE.

-- SQL Server IIF
SELECT order_id,
       IIF(total > 100, 'Large', 'Small') AS size
FROM orders;

-- MySQL IF (same concept, different name)
SELECT order_id,
       IF(total > 100, 'Large', 'Small') AS size
FROM orders;

-- Equivalent CASE (works everywhere)
SELECT order_id,
       CASE WHEN total > 100 THEN 'Large' ELSE 'Small' END AS size
FROM orders;

Rule of thumb: use CASE for portability. Use IIF (SQL Server) or IF (MySQL) in database-specific scripts where you prefer the terser syntax, but be aware that porting the query later requires rewriting it.

NULL represents an unknown value. Any comparison involving NULL evaluates to UNKNOWN (not TRUE or FALSE) under SQL's three-valued logic. Since WHERE clauses only keep rows where the condition is TRUE, rows with NULL in a comparison are silently excluded.

-- All of these evaluate to UNKNOWN, not TRUE:
SELECT NULL = NULL;      -- UNKNOWN
SELECT NULL <> 1;        -- UNKNOWN
SELECT NULL > 0;         -- UNKNOWN
SELECT 1 = NULL;         -- UNKNOWN

-- Correct tests for NULL
SELECT NULL IS NULL;     -- TRUE
SELECT NULL IS NOT NULL; -- FALSE

-- Common mistake: missing IS NULL rows
SELECT * FROM orders WHERE discount <> 0;
-- Excludes rows where discount IS NULL — those rows have unknown discount

-- Fix: explicitly include the NULL case
SELECT * FROM orders WHERE discount <> 0 OR discount IS NULL;

Rule of thumb: whenever a column may be NULL, check whether your WHERE clause correctly handles it. Any condition using =, <>, >, <, LIKE, or IN silently drops NULL rows.

All aggregate functions (except COUNT(*)) ignore NULL values in their input. This is usually what you want, but it can produce surprising results.

-- Setup: discount column has some NULLs
-- | id | total | discount |
-- | 1  | 100   | 10       |
-- | 2  | 200   | NULL     |
-- | 3  | 300   | 20       |

SELECT COUNT(*)        AS total_rows,       -- → 3 (counts all rows)
       COUNT(discount) AS non_null_discount, -- → 2 (skips NULLs)
       SUM(discount)   AS total_discount,    -- → 30 (NULLs ignored)
       AVG(discount)   AS avg_discount       -- → 15 (30 / 2, not 30 / 3!)
FROM orders;
-- AVG denominator is the COUNT of non-NULL rows, not total rows.

-- Fix: use COALESCE to treat NULL as 0 in AVG
SELECT AVG(COALESCE(discount, 0)) AS avg_discount FROM orders; -- → 10

Rule of thumb: AVG(col) divides by the count of non-NULL values — this can silently exclude missing data from the average. When NULLs should be treated as 0 in averages, use AVG(COALESCE(col, 0)).

Conditional aggregation uses a CASE expression inside an aggregate function to count, sum, or average only rows matching a condition — pivoting row data into columns without a JOIN.

-- Count orders by status in a single pass
SELECT COUNT(*)                                         AS total,
       COUNT(CASE WHEN status = 'pending'   THEN 1 END) AS pending,
       COUNT(CASE WHEN status = 'shipped'   THEN 1 END) AS shipped,
       COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
       SUM(CASE WHEN status = 'pending' THEN total ELSE 0 END) AS pending_revenue
FROM orders;

-- Postgres / SQL Server shorthand: FILTER clause
SELECT COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
       COUNT(*) FILTER (WHERE status = 'shipped')   AS shipped,
       SUM(total) FILTER (WHERE status = 'pending') AS pending_revenue
FROM orders;

Rule of thumb: use conditional aggregation to pivot data in a single query instead of multiple subqueries or UNION. The FILTER clause (Postgres/SQL Server) is cleaner than CASE WHEN … THEN 1 END — prefer it when available.

GREATEST(val1, val2, …) returns the largest value from its arguments. LEAST(val1, val2, …) returns the smallest. They work across any comparable types and return NULL if any argument is NULL (Postgres/MySQL); SQL Server does not have these functions natively.

SELECT GREATEST(10, 20, 5);          -- → 20
SELECT LEAST(10, 20, 5);             -- → 5
SELECT GREATEST(NULL, 10, 20);       -- → NULL (any NULL propagates)

-- Practical: clamp a value within a min/max range
SELECT LEAST(GREATEST(user_rating, 1), 5) AS clamped_rating FROM reviews;
-- Ensures rating is always between 1 and 5

-- Use COALESCE to handle NULLs when comparing columns
SELECT GREATEST(COALESCE(a, 0), COALESCE(b, 0)) AS max_ab FROM t;

SQL Server equivalent using CASE:

SELECT CASE WHEN a > b THEN a ELSE b END AS greatest_ab FROM t;

Rule of thumb: use GREATEST/LEAST for clamping values to a range or comparing columns from the same row — they are cleaner than nested CASE expressions for this pattern.

Standard = returns UNKNOWN when either side is NULL. To check whether two nullable columns are "equal" (including both being NULL), use database- specific NULL-safe equality.

-- Standard SQL: verbose but portable
SELECT *
FROM   t1
JOIN   t2 ON (t1.col = t2.col) OR (t1.col IS NULL AND t2.col IS NULL);

-- Postgres: IS NOT DISTINCT FROM (NULL-safe equality)
SELECT * FROM t1 JOIN t2 ON t1.col IS NOT DISTINCT FROM t2.col;
-- NULL IS NOT DISTINCT FROM NULL → TRUE
-- 1   IS NOT DISTINCT FROM 1    → TRUE
-- 1   IS NOT DISTINCT FROM NULL → FALSE

-- MySQL: <=> (spaceship operator)
SELECT * FROM t1 JOIN t2 ON t1.col <=> t2.col;
-- NULL <=> NULL → 1 (TRUE)

-- SQL Server: no shorthand — use the verbose ANSI form

Rule of thumb: use IS NOT DISTINCT FROM (Postgres) or <=> (MySQL) when joining or comparing nullable columns where two NULLs should be considered equal. This is common in upsert logic and change-detection queries.

SQL uses three truth values: TRUE, FALSE, and UNKNOWN (NULL). AND and OR follow specific rules when UNKNOWN is involved:

TRUE  AND UNKNOWN = UNKNOWN
FALSE AND UNKNOWN = FALSE     ← FALSE wins in AND
TRUE  OR  UNKNOWN = TRUE      ← TRUE wins in OR
FALSE OR  UNKNOWN = UNKNOWN
NOT UNKNOWN       = UNKNOWN

-- Pitfall: NOT IN with a NULL in the subquery
SELECT * FROM products
WHERE  id NOT IN (SELECT product_id FROM discontinued WHERE product_id IS NULL);
-- If the subquery returns even one NULL, NOT IN always returns UNKNOWN
-- → zero rows returned! (UNKNOWN is treated as FALSE in WHERE)

-- Fix: use NOT EXISTS instead
SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM discontinued d WHERE d.product_id = p.id
);
-- NOT EXISTS correctly handles NULLs — returns TRUE if no match found

Rule of thumb: never use NOT IN (subquery) when the subquery can return NULLs — it silently returns zero rows. Always use NOT EXISTS as the safe alternative.

For mapping one value to another across many cases, CASE is the standard approach. Some databases also offer compact alternatives.

-- Standard CASE — readable and portable
SELECT status,
       CASE status
         WHEN 1 THEN 'Active'
         WHEN 2 THEN 'Inactive'
         WHEN 3 THEN 'Banned'
         ELSE        'Unknown'
       END AS status_label
FROM users;

-- Oracle DECODE (not available in other databases)
SELECT DECODE(status, 1, 'Active', 2, 'Inactive', 3, 'Banned', 'Unknown')
FROM users;

-- Postgres: use a lookup table or CASE — no DECODE
-- Alternative: join to a status lookup table (preferred for long lists)
SELECT u.id, s.label
FROM users u
JOIN status_codes s ON s.code = u.status;

Rule of thumb: for short, stable mappings (< 6 values), use CASE. For longer or changeable mappings, maintain a lookup/reference table and join to it — the mapping is then data, not code, and can be updated without a schema change.

If a string column contains non-numeric values and you try to CAST it to a number, the query fails. SQL Server and MySQL offer safe-cast functions that return NULL on failure. Postgres requires a different approach.

-- SQL Server: TRY_CAST / TRY_CONVERT (return NULL on failure)
SELECT TRY_CAST('123'   AS INT);        -- → 123
SELECT TRY_CAST('abc'   AS INT);        -- → NULL (no error)
SELECT TRY_CONVERT(DATE, '2026-06-20'); -- → '2026-06-20'
SELECT TRY_CONVERT(DATE, 'not-a-date'); -- → NULL

-- MySQL: CAST silently coerces and produces 0 or NULL
SELECT CAST('abc' AS UNSIGNED);  -- → 0 (silent coercion, with a warning)

-- Postgres: no TRY_CAST built-in; use regexp check before casting
SELECT CASE
         WHEN value ~ '^\d+$' THEN value::INT
         ELSE NULL
       END AS safe_int
FROM input_data;
-- Or wrap in a PL/pgSQL function that catches exceptions

Rule of thumb: validate and sanitise data at the application boundary before it enters the database. When cleaning dirty data inside SQL, use TRY_CAST (SQL Server) or a regex guard (Postgres) to avoid query-aborting cast errors.

CASE inside ORDER BY lets you define a custom sort priority that is not possible with a simple column sort — for example, putting a specific status first, sorting NULL values to the bottom, or combining multiple conditions.

-- Sort 'pending' orders first, then 'shipped', then all others alphabetically
SELECT order_id, status
FROM   orders
ORDER  BY CASE status
            WHEN 'pending' THEN 1
            WHEN 'shipped' THEN 2
            ELSE 3
          END,
         status ASC;  -- secondary sort within the ELSE group

-- Sort NULLs to the bottom (Postgres also has NULLS LAST natively)
SELECT id, priority
FROM   tasks
ORDER  BY CASE WHEN priority IS NULL THEN 1 ELSE 0 END,
         priority ASC;

-- Postgres native null ordering (cleaner):
SELECT id, priority FROM tasks ORDER BY priority ASC NULLS LAST;

Rule of thumb: use CASE in ORDER BY when the sort requirement cannot be expressed as ASC/DESC on existing columns. For NULL ordering specifically, prefer NULLS FIRST / NULLS LAST in databases that support it (Postgres, Oracle, SQL Server 2022+) — it is more readable.

Pivoting transforms row values into column headers. SQL lacks a native PIVOT syntax in all databases, but conditional aggregation with CASE achieves the same result and is portable.

-- Data: (year, quarter, revenue)
-- Goal: one row per year with columns q1, q2, q3, q4

SELECT year,
       SUM(CASE WHEN quarter = 1 THEN revenue END) AS q1,
       SUM(CASE WHEN quarter = 2 THEN revenue END) AS q2,
       SUM(CASE WHEN quarter = 3 THEN revenue END) AS q3,
       SUM(CASE WHEN quarter = 4 THEN revenue END) AS q4
FROM   quarterly_revenue
GROUP  BY year
ORDER  BY year;

-- SQL Server native PIVOT syntax (not portable):
SELECT year, [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM   quarterly_revenue
PIVOT (SUM(revenue) FOR quarter IN ([1],[2],[3],[4])) AS p;

Rule of thumb: use CASE-based conditional aggregation for portability and readability. Use SQL Server's PIVOT syntax only in SQL Server-specific code where dynamic pivot (unknown column count) is needed — even then, it requires dynamic SQL to handle a variable number of pivot columns.