Bits of .NET

📋 COALESCE is NVL2, ISNULL, and IFNULL Combined

Need first non-null value? COALESCE takes multiple arguments, returns first non-null. Works across all databases.

📝 Basic COALESCE

-- Return first non-null
SELECT COALESCE(null, null, 'first', 'second');
-- Returns 'first'

SELECT COALESCE(phone, mobile, email, 'No contact');
-- Returns phone if not null, else mobile, else email, else default

-- Default value for NULL
SELECT name, COALESCE(age, 0) as age FROM users;

-- Multiple columns preference
SELECT 
    COALESCE(work_email, personal_email, 'no-email') as contact_email
FROM employees;

🎯 Real-World Examples

-- Priority contact
SELECT 
    name,
    COALESCE(mobile, home_phone, work_phone, 'No phone') as primary_phone
FROM contacts;

-- Price calculation (list > sale > cost)
SELECT 
    product_name,
    COALESCE(sale_price, list_price, cost_price) as final_price
FROM products;

-- User display name (nickname > first name > username)
SELECT 
    COALESCE(nickname, first_name, username) as display_name
FROM users;

-- Pivot with defaults
SELECT 
    id,
    COALESCE(col1, col2, col3, 0) as value
FROM data;

💡 COALESCE vs ISNULL

• COALESCE: ANSI standard, works everywhere, N arguments
• ISNULL: SQL Server only, 2 arguments only
• NVL: Oracle only, 2 arguments
• IFNULL: MySQL only, 2 arguments
• COALESCE is portable: Use it for cross-database queries