SQL Null Handling

SQL COALESCE Function: Handle NULL Values (All Databases)

SQL COALESCE function returns the first non-NULL value from a list. The universal NULL handler that works in all databases.

Mar 12, 20264 min read

Overview

COALESCE returns the first non-NULL value from a list of arguments. It is the SQL standard function for NULL handling and works in every database.

All Databases

-- COALESCE returns first non-NULL:
SELECT COALESCE(NULL, NULL, 'Hello', 'World');  -- 'Hello'

-- Default value for NULL columns:
SELECT name, COALESCE(phone, 'No phone') AS phone FROM users;

-- Chain of fallbacks:
SELECT
  name,
  COALESCE(mobile_phone, work_phone, home_phone, 'N/A') AS contact
FROM contacts;

-- In calculations (NULL + anything = NULL):
SELECT
  product,
  price,
  COALESCE(discount, 0) AS discount,
  price - COALESCE(discount, 0) AS final_price
FROM products;

-- Database-specific alternatives:
-- MySQL:      IFNULL(expr, default)
-- SQL Server: ISNULL(expr, default)
-- Oracle:     NVL(expr, default)
-- PostgreSQL: COALESCE (standard, preferred)

SELECT
  IFNULL(phone, 'N/A'),       -- MySQL
  ISNULL(phone, 'N/A'),       -- SQL Server
  NVL(phone, 'N/A'),          -- Oracle
  COALESCE(phone, 'N/A')      -- All databases

Skip the Syntax Lookup

Instead of memorizing COALESCE syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

What does COALESCE do in SQL?

COALESCE returns the first non-NULL value from a list of arguments. COALESCE(NULL, NULL, 'hello') returns 'hello'. It's the standard way to provide default values for NULL columns.

What is the difference between COALESCE, IFNULL, ISNULL, and NVL?

They all replace NULL with a default. COALESCE is SQL standard (works everywhere) and accepts multiple arguments. IFNULL (MySQL), ISNULL (SQL Server), and NVL (Oracle) accept only 2 arguments.

Can AI2SQL handle NULL values?

Yes. AI2SQL automatically uses COALESCE when needed. Just describe your query and it handles NULLs correctly.