COALESCE and NVL: Providing… — NULL Handling in SQL | OneNoughtOne

0/86

Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

5 / 5

COALESCE and NVL: Providing Default Values

Transforming NULL into Meaningful Values

We've learned that NULL represents missing information and that three-valued logic governs how NULL propagates through expressions. But in practical applications, we often need to replace NULL with a concrete value —a default, a placeholder, or a fallback. This is where NULL-handling functions come in: COALESCE — The ANSI SQL standard function, supported by all major databases NVL — Oracle's proprietary equivalent, widely used in Oracle environments IFNULL / ISNULL — Vendor-specific alternatives in MySQL and SQL Server These functions transform NULL uncertainty into definite values, making calculations possible, displays cleaner, and business logic predictable.

What You Will Learn

By the end of this page, you will master COALESCE and NVL in depth—understanding their syntax, subtle behavioral differences, performance implications, advanced patterns for chaining fallbacks, and best practices for choosing between them.

COALESCE Fundamentals

COALESCE is an ANSI SQL standard function that returns the first non-NULL value from a list of expressions. It's universally supported across PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and all other major databases. Syntax: COALESCE(expression1, expression2, ..., expressionN) Behavior: Evaluates expressions from left to right Returns the first expression that evaluates to non-NULL If all expressions are NULL, returns NULL Can accept any number of arguments (minimum 2)

coalesce_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Basic COALESCE usage
 
-- Provide a default value when column is NULL
SELECT 
    customer_id,
    first_name,
    COALESCE(middle_name, '') AS middle_name,  -- Empty string if NULL
    last_name,
    COALESCE(phone, 'No phone on file') AS phone,
    COALESCE(email, 'No email on file') AS email
FROM customers;
 
-- Numeric default values
SELECT 
    product_id,
    product_name,
    COALESCE(discount, 0) AS discount,        -- 0 if no discount
    COALESCE(stock_quantity, 0) AS stock,     -- 0 if unknown stock
    price * (1 - COALESCE(discount, 0) / 100) AS final_price
FROM products;
 
-- Date default values
SELECT 
    order_id,
    order_date,
    COALESCE(ship_date, CURRENT_DATE) AS ship_date_or_today,
    COALESCE(delivery_date, ship_date, order_date) AS best_known_date
FROM orders;
 
-- Boolean-like default values
SELECT 
    user_id,
    username,
    COALESCE(is_active, TRUE) AS is_active,
    COALESCE(is_admin, FALSE) AS is_admin
FROM users;

COALESCE for Safe Calculations

COALESCE is essential for calculations involving nullable columns. Since NULL propagates through arithmetic (5 + NULL = NULL), wrapping nullable operands with COALESCE ensures you get a numeric result: price * COALESCE(quantity, 0) returns 0 when quantity is NULL, not NULL.

COALESCE Chaining: Multiple Fallback Values

One of COALESCE's most powerful features is the ability to specify multiple fallback expressions. SQL evaluates them in order until one returns non-NULL.

coalesce_chaining.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- Multiple fallback values for contact information
SELECT 
    customer_id,
    -- Try work phone, then mobile, then home, then 'No phone'
    COALESCE(work_phone, mobile_phone, home_phone, 'No phone') AS best_phone,
    -- Try personal email, then work email, then 'No email'
    COALESCE(personal_email, work_email, 'No email') AS best_email
FROM customer_contacts;
 
-- Hierarchical address fallback
SELECT 
    customer_id,
    COALESCE(
        shipping_address,      -- First choice: explicit shipping address
        billing_address,       -- Second choice: billing address
        primary_address,       -- Third choice: primary address
        'Address Required'     -- Final fallback: placeholder
    ) AS delivery_address
FROM customers;
 
-- Cascading name display
SELECT 
    user_id,
    COALESCE(
        display_name,          -- User's chosen display name
        username,              -- Username if no display name
        email,                 -- Email if no username
        'Anonymous User'       -- Last resort
    ) AS shown_name
FROM users;
 
-- Fallback calculation sources
SELECT 
    product_id,
    product_name,
    COALESCE(
        promotional_price,     -- Use promo price if running
        sale_price,            -- Use sale price if on sale
        regular_price,         -- Use regular price
        0                      -- Emergency fallback (shouldn't happen)
    ) AS effective_price
FROM products;
 
-- Fallback from related tables via subqueries
SELECT 
    e.employee_id,
    e.name,
    COALESCE(
        e.direct_manager_id,
        e.department_default_manager_id,
        (
            SELECT id FROM employees 
            WHERE role = 'CEO' 
            LIMIT 1
        )
    ) AS effective_manager_id
FROM employees e;

Order Matters

COALESCE stops at the first non-NULL value. Order your expressions from most preferred to least preferred. Each expression is only evaluated if all previous expressions were NULL, which can impact performance if expressions are expensive (like subqueries).

NVL: Oracle's NULL Handling Function

NVL is Oracle's proprietary function for handling NULL values. It predates the ANSI COALESCE and remains widely used in Oracle environments. Syntax: NVL(expression, replacement_value) Behavior: Takes exactly two arguments Returns expression if it's not NULL Returns replacement_value if expression is NULL

nvl_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Basic NVL usage (Oracle)
 
-- Replace NULL with default value
SELECT 
    employee_id,
    first_name,
    last_name,
    NVL(commission_pct, 0) AS commission_pct,
    salary + (salary * NVL(commission_pct, 0)) AS total_compensation
FROM employees;
 
-- String default
SELECT 
    customer_id,
    NVL(phone_number, 'Not Available') AS phone,
    NVL(email, 'Not Available') AS email
FROM customers;
 
-- Date default
SELECT 
    order_id,
    order_date,
    NVL(ship_date, SYSDATE) AS ship_or_today
FROM orders;
 
-- Nested NVL for multiple fallbacks (less elegant than COALESCE)
SELECT 
    customer_id,
    NVL(work_phone, NVL(mobile_phone, NVL(home_phone, 'No phone'))) AS best_phone
FROM customer_contacts;
-- Compare to COALESCE:
-- COALESCE(work_phone, mobile_phone, home_phone, 'No phone')  -- Much cleaner!

NVL vs COALESCE: Key Differences

While NVL and COALESCE seem similar, they have important differences: (1) NVL takes exactly 2 arguments; COALESCE takes any number. (2) In Oracle, NVL always evaluates both arguments; COALESCE short-circuits. (3) NVL's data type rules differ slightly from COALESCE. (4) COALESCE is ANSI standard; NVL is Oracle-specific.

NVL2: Oracle's Extended NULL Check Oracle also provides NVL2, which takes three arguments and returns different values based on whether the first is NULL or non-NULL:

nvl2_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- NVL2 syntax: NVL2(expression, value_if_not_null, value_if_null)
 
-- Display different text based on NULL status
SELECT 
    employee_id,
    first_name,
    commission_pct,
    NVL2(commission_pct, 'Has Commission', 'No Commission') AS commission_status,
    NVL2(manager_id, 'Has Manager', 'Top Level') AS manager_status
FROM employees;
 
-- Calculate differently based on NULL
SELECT 
    product_id,
    product_name,
    -- If discount exists, apply it; otherwise use original price
    NVL2(discount_pct, price * (1 - discount_pct/100), price) AS final_price
FROM products;
 
-- Equivalent using CASE (works in all databases)
SELECT 
    product_id,
    product_name,
    CASE 
        WHEN discount_pct IS NOT NULL THEN price * (1 - discount_pct/100)
        ELSE price
    END AS final_price
FROM products;

Vendor-Specific NULL Functions

Different database systems provide their own NULL-handling functions. Understanding these helps when working with specific platforms or porting queries between systems.

NULL-Handling Functions by Database
Database	Function	Equivalent To
All (ANSI)	`COALESCE(a, b, c, ...)`	First non-NULL value
Oracle	`NVL(a, b)`	`COALESCE(a, b)` (but always evaluates both)
Oracle	`NVL2(a, b, c)`	`CASE WHEN a IS NOT NULL THEN b ELSE c END`
MySQL	`IFNULL(a, b)`	`COALESCE(a, b)`
SQL Server	`ISNULL(a, b)`	`COALESCE(a, b)` (with type differences)
PostgreSQL	`COALESCE(a, b, ...)`	Standard COALESCE
SQLite	`IFNULL(a, b)` or `COALESCE`	Both supported

vendor_functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- MySQL: IFNULL
SELECT 
    customer_id,
    IFNULL(phone, 'No phone') AS phone,
    IFNULL(email, 'No email') AS email
FROM customers;
 
-- SQL Server: ISNULL (note: different from IS NULL predicate!)
SELECT 
    customer_id,
    ISNULL(phone, 'No phone') AS phone,
    ISNULL(email, 'No email') AS email
FROM customers;
 
-- SQL Server: ISNULL has type quirks
-- ISNULL determines return type from first argument only
SELECT ISNULL(NULL, 'Hello World');  
-- Returns NULL because first arg is NULL and has no type context!
 
-- COALESCE determines type from all arguments
SELECT COALESCE(NULL, 'Hello World');  
-- Returns 'Hello World' correctly
 
-- Best Practice: Use COALESCE everywhere for portability
-- If you must use ISNULL in SQL Server, be careful with types:
DECLARE @value VARCHAR(100) = NULL;
SELECT ISNULL(@value, 'Default');  -- Works because @value has defined type

Prefer COALESCE for Portability

Unless you're writing Oracle-only code and need NVL's specific behavior, use COALESCE. It's ANSI standard, supported everywhere, accepts multiple arguments, and has predictable short-circuit evaluation. This makes your SQL portable and your intent clear.

Type Handling and Coercion

COALESCE and similar functions must determine the return type when given arguments of different types. Understanding these rules prevents unexpected errors or truncation.

type_coercion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- COALESCE determines return type from all arguments
 
-- Mixing numeric types: result uses the highest precision
SELECT COALESCE(integer_col, decimal_col, 0.00);  
-- Returns DECIMAL to accommodate all possible values
 
-- Mixing string lengths: result accommodates longest
SELECT COALESCE(varchar_50_col, varchar_100_col, 'default');  
-- Returns VARCHAR that fits all values
 
-- Type mismatch errors
-- This fails because types are incompatible:
SELECT COALESCE(date_column, 'no date');  -- ERROR in strict databases
-- Fix: Cast to string explicitly
SELECT COALESCE(CAST(date_column AS VARCHAR), 'no date');
 
-- Or provide typed NULL as first argument to set return type
SELECT COALESCE(
    date_column,
    CAST(NULL AS DATE),  -- Ensures DATE type even if first is NULL
    CURRENT_DATE
);
 
-- Oracle NVL has stricter type requirements
-- If first argument is NULL literal, must cast:
SELECT NVL(NULL, 'default') FROM dual;  -- May error or behave unexpectedly
SELECT NVL(CAST(NULL AS VARCHAR2(100)), 'default') FROM dual;  -- Safe
 
-- Practical example: Format nullable numbers safely
SELECT 
    product_id,
    product_name,
    '$' || CAST(COALESCE(price, 0) AS VARCHAR(20)) AS display_price,
    COALESCE(CAST(quantity AS VARCHAR(10)), 'N/A') AS display_quantity
FROM products;

String Length Truncation

In some databases (especially SQL Server with ISNULL), the return type is determined by the first argument. If the first argument is VARCHAR(10) and the fallback is longer, the result may be truncated. COALESCE considers all arguments when determining the result type, making it safer for strings.

Performance Considerations

COALESCE and NVL have performance implications that should influence how you use them, particularly with complex expressions or high-volume queries.

Performance Factors

•Short-Circuit Evaluation — COALESCE typically stops evaluating once it finds a non-NULL value. Oracle's NVL may evaluate both arguments regardless. This matters when arguments are expensive (function calls, subqueries).
•Index Usage — Using COALESCE on indexed columns in WHERE clauses may prevent index usage. WHERE COALESCE(col, 0) > 5 likely won't use an index on col.
•Expression Complexity — Each COALESCE argument is evaluated (at least potentially). Keep expensive operations later in the argument list.
•Constant Folding — When the default value is a constant, the optimizer can sometimes fold it. But COALESCE with function calls prevents this optimization.

performance_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Anti-pattern: COALESCE in WHERE prevents index usage
SELECT * FROM orders
WHERE COALESCE(customer_id, 0) = 123;
-- The index on customer_id cannot be used!
 
-- Better: Use explicit NULL handling
SELECT * FROM orders
WHERE customer_id = 123;  -- Uses index
-- Handle NULL case separately if needed:
SELECT * FROM orders
WHERE customer_id = 123 OR (customer_id IS NULL AND 0 = 123);
 
-- Anti-pattern: Expensive operation evaluated even when not needed (NVL)
-- Oracle NVL evaluates both arguments:
SELECT NVL(cached_value, expensive_calculation()) FROM table;
-- expensive_calculation() runs even when cached_value is not NULL!
 
-- Better: Use COALESCE (short-circuits) or CASE
SELECT COALESCE(cached_value, expensive_calculation()) FROM table;
-- expensive_calculation() only runs when cached_value IS NULL
 
-- Or explicitly with CASE:
SELECT 
    CASE 
        WHEN cached_value IS NOT NULL THEN cached_value
        ELSE expensive_calculation()
    END
FROM table;
 
-- Order expressions by likelihood and cost
-- Most common case first, expensive operation last:
SELECT 
    COALESCE(
        frequently_populated_column,      -- Check this first
        sometimes_populated_column,       -- Then this
        (SELECT value FROM config_table)  -- Subquery last
    ) AS result
FROM main_table;
 
-- Computed column to avoid repeated COALESCE
-- If you frequently query COALESCE(nullable_col, default):
ALTER TABLE products 
ADD effective_price AS COALESCE(sale_price, regular_price) PERSISTED;
-- Now queries can use the computed column directly with index support

Oracle NVL Evaluation

In Oracle, NVL always evaluates both arguments. This is a documented behavior difference from COALESCE. If your second argument is expensive (function call, subquery), prefer COALESCE or use CASE with explicit IS NULL check for guaranteed short-circuit behavior.

Advanced COALESCE Patterns

Beyond simple default values, COALESCE enables sophisticated patterns for data transformation, reporting, and business logic.

advanced_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- PATTERN 1: Dynamic column selection
-- Display whichever value is available
SELECT 
    order_id,
    COALESCE(
        special_instructions,
        shipping_notes,
        customer_notes,
        'No special instructions'
    ) AS instructions
FROM orders;
 
-- PATTERN 2: Aggregate NULL handling
-- Replace NULL aggregate results with meaningful defaults
SELECT 
    department_id,
    COALESCE(SUM(salary), 0) AS total_salary,
    COALESCE(AVG(salary), 0) AS avg_salary,
    COALESCE(MAX(hire_date), '1900-01-01') AS earliest_hire
FROM employees
WHERE department_id = 999  -- Non-existent department
GROUP BY department_id;
-- Without COALESCE, empty group returns NULLs
 
-- PATTERN 3: Pivoting with COALESCE
SELECT 
    product_id,
    COALESCE(MAX(CASE WHEN region = 'North' THEN sales END), 0) AS north_sales,
    COALESCE(MAX(CASE WHEN region = 'South' THEN sales END), 0) AS south_sales,
    COALESCE(MAX(CASE WHEN region = 'East' THEN sales END), 0) AS east_sales,
    COALESCE(MAX(CASE WHEN region = 'West' THEN sales END), 0) AS west_sales
FROM regional_sales
GROUP BY product_id;
 
-- PATTERN 4: Concatenation with NULL safety
SELECT 
    customer_id,
    COALESCE(first_name, '') || ' ' || 
    COALESCE(middle_name || ' ', '') || 
    COALESCE(last_name, '') AS full_name
FROM customers;
-- Handles any combination of NULL name parts
 
-- PATTERN 5: Conditional calculations
SELECT 
    order_id,
    subtotal,
    COALESCE(discount_amount, 0) AS discount,
    COALESCE(tax_amount, subtotal * 0.08) AS tax,  -- Default tax rate if not specified
    COALESCE(shipping_cost, 
        CASE WHEN subtotal > 100 THEN 0 ELSE 9.99 END  -- Free shipping over $100
    ) AS shipping,
    subtotal - COALESCE(discount_amount, 0) + 
    COALESCE(tax_amount, subtotal * 0.08) + 
    COALESCE(shipping_cost, CASE WHEN subtotal > 100 THEN 0 ELSE 9.99 END) AS total
FROM orders;
 
-- PATTERN 6: Reporting friendly NULLs
SELECT 
    d.department_name,
    COALESCE(CAST(COUNT(e.employee_id) AS VARCHAR), '0') AS employee_count,
    COALESCE(CAST(AVG(e.salary) AS VARCHAR(20)), 'N/A') AS avg_salary,
    COALESCE(m.name, 'No Manager Assigned') AS manager_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN employees m ON d.manager_id = m.id
GROUP BY d.department_name, m.name;
 
-- PATTERN 7: UPSERT-style merge logic
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
    t.name = COALESCE(s.name, t.name),  -- Keep old if new is NULL
    t.email = COALESCE(s.email, t.email),
    t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (id, name, email, created_at)
    VALUES (s.id, s.name, s.email, CURRENT_TIMESTAMP);

NULL-Preserving Updates

The MERGE pattern COALESCE(new_value, old_value) is powerful for partial updates. If the new value is NULL, keep the existing value. This is useful for API-driven updates where clients only send changed fields.

Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

5 / 5

COALESCE and NVL: Providing Default Values

Transforming NULL into Meaningful Values

We've learned that NULL represents missing information and that three-valued logic governs how NULL propagates through expressions. But in practical applications, we often need to replace NULL with a concrete value —a default, a placeholder, or a fallback. This is where NULL-handling functions come in: COALESCE — The ANSI SQL standard function, supported by all major databases NVL — Oracle's proprietary equivalent, widely used in Oracle environments IFNULL / ISNULL — Vendor-specific alternatives in MySQL and SQL Server These functions transform NULL uncertainty into definite values, making calculations possible, displays cleaner, and business logic predictable.

What You Will Learn

By the end of this page, you will master COALESCE and NVL in depth—understanding their syntax, subtle behavioral differences, performance implications, advanced patterns for chaining fallbacks, and best practices for choosing between them.

COALESCE Fundamentals

COALESCE is an ANSI SQL standard function that returns the first non-NULL value from a list of expressions. It's universally supported across PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and all other major databases. Syntax: COALESCE(expression1, expression2, ..., expressionN) Behavior: Evaluates expressions from left to right Returns the first expression that evaluates to non-NULL If all expressions are NULL, returns NULL Can accept any number of arguments (minimum 2)

coalesce_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Basic COALESCE usage
 
-- Provide a default value when column is NULL
SELECT 
    customer_id,
    first_name,
    COALESCE(middle_name, '') AS middle_name,  -- Empty string if NULL
    last_name,
    COALESCE(phone, 'No phone on file') AS phone,
    COALESCE(email, 'No email on file') AS email
FROM customers;
 
-- Numeric default values
SELECT 
    product_id,
    product_name,
    COALESCE(discount, 0) AS discount,        -- 0 if no discount
    COALESCE(stock_quantity, 0) AS stock,     -- 0 if unknown stock
    price * (1 - COALESCE(discount, 0) / 100) AS final_price
FROM products;
 
-- Date default values
SELECT 
    order_id,
    order_date,
    COALESCE(ship_date, CURRENT_DATE) AS ship_date_or_today,
    COALESCE(delivery_date, ship_date, order_date) AS best_known_date
FROM orders;
 
-- Boolean-like default values
SELECT 
    user_id,
    username,
    COALESCE(is_active, TRUE) AS is_active,
    COALESCE(is_admin, FALSE) AS is_admin
FROM users;

COALESCE for Safe Calculations

COALESCE is essential for calculations involving nullable columns. Since NULL propagates through arithmetic (5 + NULL = NULL), wrapping nullable operands with COALESCE ensures you get a numeric result: price * COALESCE(quantity, 0) returns 0 when quantity is NULL, not NULL.

COALESCE Chaining: Multiple Fallback Values

One of COALESCE's most powerful features is the ability to specify multiple fallback expressions. SQL evaluates them in order until one returns non-NULL.

coalesce_chaining.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- Multiple fallback values for contact information
SELECT 
    customer_id,
    -- Try work phone, then mobile, then home, then 'No phone'
    COALESCE(work_phone, mobile_phone, home_phone, 'No phone') AS best_phone,
    -- Try personal email, then work email, then 'No email'
    COALESCE(personal_email, work_email, 'No email') AS best_email
FROM customer_contacts;
 
-- Hierarchical address fallback
SELECT 
    customer_id,
    COALESCE(
        shipping_address,      -- First choice: explicit shipping address
        billing_address,       -- Second choice: billing address
        primary_address,       -- Third choice: primary address
        'Address Required'     -- Final fallback: placeholder
    ) AS delivery_address
FROM customers;
 
-- Cascading name display
SELECT 
    user_id,
    COALESCE(
        display_name,          -- User's chosen display name
        username,              -- Username if no display name
        email,                 -- Email if no username
        'Anonymous User'       -- Last resort
    ) AS shown_name
FROM users;
 
-- Fallback calculation sources
SELECT 
    product_id,
    product_name,
    COALESCE(
        promotional_price,     -- Use promo price if running
        sale_price,            -- Use sale price if on sale
        regular_price,         -- Use regular price
        0                      -- Emergency fallback (shouldn't happen)
    ) AS effective_price
FROM products;
 
-- Fallback from related tables via subqueries
SELECT 
    e.employee_id,
    e.name,
    COALESCE(
        e.direct_manager_id,
        e.department_default_manager_id,
        (
            SELECT id FROM employees 
            WHERE role = 'CEO' 
            LIMIT 1
        )
    ) AS effective_manager_id
FROM employees e;

Order Matters

COALESCE stops at the first non-NULL value. Order your expressions from most preferred to least preferred. Each expression is only evaluated if all previous expressions were NULL, which can impact performance if expressions are expensive (like subqueries).

NVL: Oracle's NULL Handling Function

NVL is Oracle's proprietary function for handling NULL values. It predates the ANSI COALESCE and remains widely used in Oracle environments. Syntax: NVL(expression, replacement_value) Behavior: Takes exactly two arguments Returns expression if it's not NULL Returns replacement_value if expression is NULL

nvl_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Basic NVL usage (Oracle)
 
-- Replace NULL with default value
SELECT 
    employee_id,
    first_name,
    last_name,
    NVL(commission_pct, 0) AS commission_pct,
    salary + (salary * NVL(commission_pct, 0)) AS total_compensation
FROM employees;
 
-- String default
SELECT 
    customer_id,
    NVL(phone_number, 'Not Available') AS phone,
    NVL(email, 'Not Available') AS email
FROM customers;
 
-- Date default
SELECT 
    order_id,
    order_date,
    NVL(ship_date, SYSDATE) AS ship_or_today
FROM orders;
 
-- Nested NVL for multiple fallbacks (less elegant than COALESCE)
SELECT 
    customer_id,
    NVL(work_phone, NVL(mobile_phone, NVL(home_phone, 'No phone'))) AS best_phone
FROM customer_contacts;
-- Compare to COALESCE:
-- COALESCE(work_phone, mobile_phone, home_phone, 'No phone')  -- Much cleaner!

NVL vs COALESCE: Key Differences

While NVL and COALESCE seem similar, they have important differences: (1) NVL takes exactly 2 arguments; COALESCE takes any number. (2) In Oracle, NVL always evaluates both arguments; COALESCE short-circuits. (3) NVL's data type rules differ slightly from COALESCE. (4) COALESCE is ANSI standard; NVL is Oracle-specific.

NVL2: Oracle's Extended NULL Check Oracle also provides NVL2, which takes three arguments and returns different values based on whether the first is NULL or non-NULL:

nvl2_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- NVL2 syntax: NVL2(expression, value_if_not_null, value_if_null)
 
-- Display different text based on NULL status
SELECT 
    employee_id,
    first_name,
    commission_pct,
    NVL2(commission_pct, 'Has Commission', 'No Commission') AS commission_status,
    NVL2(manager_id, 'Has Manager', 'Top Level') AS manager_status
FROM employees;
 
-- Calculate differently based on NULL
SELECT 
    product_id,
    product_name,
    -- If discount exists, apply it; otherwise use original price
    NVL2(discount_pct, price * (1 - discount_pct/100), price) AS final_price
FROM products;
 
-- Equivalent using CASE (works in all databases)
SELECT 
    product_id,
    product_name,
    CASE 
        WHEN discount_pct IS NOT NULL THEN price * (1 - discount_pct/100)
        ELSE price
    END AS final_price
FROM products;

Vendor-Specific NULL Functions

Different database systems provide their own NULL-handling functions. Understanding these helps when working with specific platforms or porting queries between systems.

NULL-Handling Functions by Database
Database	Function	Equivalent To
All (ANSI)	`COALESCE(a, b, c, ...)`	First non-NULL value
Oracle	`NVL(a, b)`	`COALESCE(a, b)` (but always evaluates both)
Oracle	`NVL2(a, b, c)`	`CASE WHEN a IS NOT NULL THEN b ELSE c END`
MySQL	`IFNULL(a, b)`	`COALESCE(a, b)`
SQL Server	`ISNULL(a, b)`	`COALESCE(a, b)` (with type differences)
PostgreSQL	`COALESCE(a, b, ...)`	Standard COALESCE
SQLite	`IFNULL(a, b)` or `COALESCE`	Both supported

vendor_functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- MySQL: IFNULL
SELECT 
    customer_id,
    IFNULL(phone, 'No phone') AS phone,
    IFNULL(email, 'No email') AS email
FROM customers;
 
-- SQL Server: ISNULL (note: different from IS NULL predicate!)
SELECT 
    customer_id,
    ISNULL(phone, 'No phone') AS phone,
    ISNULL(email, 'No email') AS email
FROM customers;
 
-- SQL Server: ISNULL has type quirks
-- ISNULL determines return type from first argument only
SELECT ISNULL(NULL, 'Hello World');  
-- Returns NULL because first arg is NULL and has no type context!
 
-- COALESCE determines type from all arguments
SELECT COALESCE(NULL, 'Hello World');  
-- Returns 'Hello World' correctly
 
-- Best Practice: Use COALESCE everywhere for portability
-- If you must use ISNULL in SQL Server, be careful with types:
DECLARE @value VARCHAR(100) = NULL;
SELECT ISNULL(@value, 'Default');  -- Works because @value has defined type

Prefer COALESCE for Portability

Unless you're writing Oracle-only code and need NVL's specific behavior, use COALESCE. It's ANSI standard, supported everywhere, accepts multiple arguments, and has predictable short-circuit evaluation. This makes your SQL portable and your intent clear.

Type Handling and Coercion

COALESCE and similar functions must determine the return type when given arguments of different types. Understanding these rules prevents unexpected errors or truncation.

type_coercion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- COALESCE determines return type from all arguments
 
-- Mixing numeric types: result uses the highest precision
SELECT COALESCE(integer_col, decimal_col, 0.00);  
-- Returns DECIMAL to accommodate all possible values
 
-- Mixing string lengths: result accommodates longest
SELECT COALESCE(varchar_50_col, varchar_100_col, 'default');  
-- Returns VARCHAR that fits all values
 
-- Type mismatch errors
-- This fails because types are incompatible:
SELECT COALESCE(date_column, 'no date');  -- ERROR in strict databases
-- Fix: Cast to string explicitly
SELECT COALESCE(CAST(date_column AS VARCHAR), 'no date');
 
-- Or provide typed NULL as first argument to set return type
SELECT COALESCE(
    date_column,
    CAST(NULL AS DATE),  -- Ensures DATE type even if first is NULL
    CURRENT_DATE
);
 
-- Oracle NVL has stricter type requirements
-- If first argument is NULL literal, must cast:
SELECT NVL(NULL, 'default') FROM dual;  -- May error or behave unexpectedly
SELECT NVL(CAST(NULL AS VARCHAR2(100)), 'default') FROM dual;  -- Safe
 
-- Practical example: Format nullable numbers safely
SELECT 
    product_id,
    product_name,
    '$' || CAST(COALESCE(price, 0) AS VARCHAR(20)) AS display_price,
    COALESCE(CAST(quantity AS VARCHAR(10)), 'N/A') AS display_quantity
FROM products;

String Length Truncation

In some databases (especially SQL Server with ISNULL), the return type is determined by the first argument. If the first argument is VARCHAR(10) and the fallback is longer, the result may be truncated. COALESCE considers all arguments when determining the result type, making it safer for strings.

Performance Considerations

COALESCE and NVL have performance implications that should influence how you use them, particularly with complex expressions or high-volume queries.

Performance Factors

•Short-Circuit Evaluation — COALESCE typically stops evaluating once it finds a non-NULL value. Oracle's NVL may evaluate both arguments regardless. This matters when arguments are expensive (function calls, subqueries).
•Index Usage — Using COALESCE on indexed columns in WHERE clauses may prevent index usage. WHERE COALESCE(col, 0) > 5 likely won't use an index on col.
•Expression Complexity — Each COALESCE argument is evaluated (at least potentially). Keep expensive operations later in the argument list.
•Constant Folding — When the default value is a constant, the optimizer can sometimes fold it. But COALESCE with function calls prevents this optimization.

performance_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Anti-pattern: COALESCE in WHERE prevents index usage
SELECT * FROM orders
WHERE COALESCE(customer_id, 0) = 123;
-- The index on customer_id cannot be used!
 
-- Better: Use explicit NULL handling
SELECT * FROM orders
WHERE customer_id = 123;  -- Uses index
-- Handle NULL case separately if needed:
SELECT * FROM orders
WHERE customer_id = 123 OR (customer_id IS NULL AND 0 = 123);
 
-- Anti-pattern: Expensive operation evaluated even when not needed (NVL)
-- Oracle NVL evaluates both arguments:
SELECT NVL(cached_value, expensive_calculation()) FROM table;
-- expensive_calculation() runs even when cached_value is not NULL!
 
-- Better: Use COALESCE (short-circuits) or CASE
SELECT COALESCE(cached_value, expensive_calculation()) FROM table;
-- expensive_calculation() only runs when cached_value IS NULL
 
-- Or explicitly with CASE:
SELECT 
    CASE 
        WHEN cached_value IS NOT NULL THEN cached_value
        ELSE expensive_calculation()
    END
FROM table;
 
-- Order expressions by likelihood and cost
-- Most common case first, expensive operation last:
SELECT 
    COALESCE(
        frequently_populated_column,      -- Check this first
        sometimes_populated_column,       -- Then this
        (SELECT value FROM config_table)  -- Subquery last
    ) AS result
FROM main_table;
 
-- Computed column to avoid repeated COALESCE
-- If you frequently query COALESCE(nullable_col, default):
ALTER TABLE products 
ADD effective_price AS COALESCE(sale_price, regular_price) PERSISTED;
-- Now queries can use the computed column directly with index support

Oracle NVL Evaluation

In Oracle, NVL always evaluates both arguments. This is a documented behavior difference from COALESCE. If your second argument is expensive (function call, subquery), prefer COALESCE or use CASE with explicit IS NULL check for guaranteed short-circuit behavior.

Advanced COALESCE Patterns

Beyond simple default values, COALESCE enables sophisticated patterns for data transformation, reporting, and business logic.

advanced_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- PATTERN 1: Dynamic column selection
-- Display whichever value is available
SELECT 
    order_id,
    COALESCE(
        special_instructions,
        shipping_notes,
        customer_notes,
        'No special instructions'
    ) AS instructions
FROM orders;
 
-- PATTERN 2: Aggregate NULL handling
-- Replace NULL aggregate results with meaningful defaults
SELECT 
    department_id,
    COALESCE(SUM(salary), 0) AS total_salary,
    COALESCE(AVG(salary), 0) AS avg_salary,
    COALESCE(MAX(hire_date), '1900-01-01') AS earliest_hire
FROM employees
WHERE department_id = 999  -- Non-existent department
GROUP BY department_id;
-- Without COALESCE, empty group returns NULLs
 
-- PATTERN 3: Pivoting with COALESCE
SELECT 
    product_id,
    COALESCE(MAX(CASE WHEN region = 'North' THEN sales END), 0) AS north_sales,
    COALESCE(MAX(CASE WHEN region = 'South' THEN sales END), 0) AS south_sales,
    COALESCE(MAX(CASE WHEN region = 'East' THEN sales END), 0) AS east_sales,
    COALESCE(MAX(CASE WHEN region = 'West' THEN sales END), 0) AS west_sales
FROM regional_sales
GROUP BY product_id;
 
-- PATTERN 4: Concatenation with NULL safety
SELECT 
    customer_id,
    COALESCE(first_name, '') || ' ' || 
    COALESCE(middle_name || ' ', '') || 
    COALESCE(last_name, '') AS full_name
FROM customers;
-- Handles any combination of NULL name parts
 
-- PATTERN 5: Conditional calculations
SELECT 
    order_id,
    subtotal,
    COALESCE(discount_amount, 0) AS discount,
    COALESCE(tax_amount, subtotal * 0.08) AS tax,  -- Default tax rate if not specified
    COALESCE(shipping_cost, 
        CASE WHEN subtotal > 100 THEN 0 ELSE 9.99 END  -- Free shipping over $100
    ) AS shipping,
    subtotal - COALESCE(discount_amount, 0) + 
    COALESCE(tax_amount, subtotal * 0.08) + 
    COALESCE(shipping_cost, CASE WHEN subtotal > 100 THEN 0 ELSE 9.99 END) AS total
FROM orders;
 
-- PATTERN 6: Reporting friendly NULLs
SELECT 
    d.department_name,
    COALESCE(CAST(COUNT(e.employee_id) AS VARCHAR), '0') AS employee_count,
    COALESCE(CAST(AVG(e.salary) AS VARCHAR(20)), 'N/A') AS avg_salary,
    COALESCE(m.name, 'No Manager Assigned') AS manager_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN employees m ON d.manager_id = m.id
GROUP BY d.department_name, m.name;
 
-- PATTERN 7: UPSERT-style merge logic
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
    t.name = COALESCE(s.name, t.name),  -- Keep old if new is NULL
    t.email = COALESCE(s.email, t.email),
    t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (id, name, email, created_at)
    VALUES (s.id, s.name, s.email, CURRENT_TIMESTAMP);

NULL-Preserving Updates

The MERGE pattern COALESCE(new_value, old_value) is powerful for partial updates. If the new value is NULL, keep the existing value. This is useful for API-driven updates where clients only send changed fields.

COALESCE and NVL: Providing Default Values

Transforming NULL into Meaningful Values

COALESCE Fundamentals

COALESCE Chaining: Multiple Fallback Values

NVL: Oracle's NULL Handling Function

Vendor-Specific NULL Functions

Type Handling and Coercion

Performance Considerations

Advanced COALESCE Patterns

There’s more to understand.

COALESCE and NVL: Providing Default Values

Transforming NULL into Meaningful Values

COALESCE Fundamentals

COALESCE Chaining: Multiple Fallback Values

NVL: Oracle's NULL Handling Function

Vendor-Specific NULL Functions

Type Handling and Coercion

Performance Considerations

Advanced COALESCE Patterns

There’s more to understand.