Eric Campbell 5 min read SQLite

How to Use COALESCE in SQLite

COALESCE is a SQL function that returns the first non-NULL value from a list of arguments. It evaluates expressions from left to right and returns as soon as it encounters a non-NULL value. If all...

Key Insights

  • COALESCE returns the first non-NULL value from its arguments, making it essential for handling missing data and providing fallback values in SQL queries
  • Unlike IFNULL which only accepts two arguments, COALESCE can evaluate multiple fallback options in a single expression, checking each argument left-to-right
  • Empty strings are not NULL in SQLite—a critical distinction that affects how COALESCE behaves and requires careful handling in real-world applications

Introduction to COALESCE

COALESCE is a SQL function that returns the first non-NULL value from a list of arguments. It evaluates expressions from left to right and returns as soon as it encounters a non-NULL value. If all arguments are NULL, COALESCE returns NULL.

The syntax is straightforward:

COALESCE(expression1, expression2, ..., expressionN)

This function is invaluable when dealing with optional data, missing values, or when you need to establish a hierarchy of fallback values. Instead of writing complex CASE statements or nested IFNULL calls, COALESCE provides a clean, readable solution for handling NULL values in your queries.

Here’s a basic demonstration:

SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null') AS result;
-- Returns: 'first non-null'

SELECT COALESCE(NULL, 42, 100) AS result;
-- Returns: 42

SELECT COALESCE(NULL, NULL, NULL) AS result;
-- Returns: NULL

COALESCE shines in real-world scenarios where data completeness varies—user profiles with optional fields, product catalogs with conditional pricing, or configuration tables with default values.

Handling NULL Values in SELECT Queries

One of the most common uses for COALESCE is providing user-friendly default values when querying tables with optional columns. Instead of displaying NULL to end users, you can substitute meaningful text or values.

Consider a users table where phone numbers and secondary emails are optional:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT,
    secondary_email TEXT
);

INSERT INTO users (name, email, phone, secondary_email) VALUES
    ('Alice Johnson', 'alice@example.com', '555-0100', NULL),
    ('Bob Smith', 'bob@example.com', NULL, 'bob.alt@example.com'),
    ('Carol White', 'carol@example.com', NULL, NULL);

Without COALESCE, querying this table returns NULL values that aren’t user-friendly:

SELECT name, email, phone, secondary_email FROM users;
-- Bob's phone shows NULL, Carol's phone and secondary_email show NULL

With COALESCE, you can provide meaningful defaults:

SELECT 
    name,
    email,
    COALESCE(phone, 'Not provided') AS phone,
    COALESCE(secondary_email, 'None') AS secondary_email
FROM users;

This query returns clean, readable results where missing data is explicitly labeled rather than shown as NULL. This approach is particularly important for reports, exports, and API responses where NULL values might cause issues in consuming applications.

Working with Multiple Fallback Options

COALESCE’s real power emerges when you need to check multiple potential sources for a value. The function evaluates arguments sequentially and returns the first non-NULL result, making it perfect for establishing priority hierarchies.

Consider an e-commerce scenario with multiple pricing strategies:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    discount_price REAL,
    sale_price REAL,
    regular_price REAL NOT NULL
);

INSERT INTO products (name, discount_price, sale_price, regular_price) VALUES
    ('Widget A', 15.99, NULL, 29.99),
    ('Widget B', NULL, 19.99, 24.99),
    ('Widget C', NULL, NULL, 34.99),
    ('Widget D', 12.99, 16.99, 22.99);

To determine the actual selling price, you want to prioritize discount prices, then sale prices, and finally fall back to regular prices:

SELECT 
    name,
    COALESCE(discount_price, sale_price, regular_price) AS selling_price,
    regular_price
FROM products;

This query produces:

  • Widget A: $15.99 (discount price)
  • Widget B: $19.99 (sale price)
  • Widget C: $34.99 (regular price)
  • Widget D: $12.99 (discount price, even though sale price exists)

The left-to-right evaluation means Widget D uses the discount price and never evaluates the sale price. This behavior is exactly what you want when establishing clear priority rules for data selection.

COALESCE vs IFNULL

SQLite provides both COALESCE and IFNULL for handling NULL values. While they overlap in functionality, understanding their differences helps you choose the right tool.

IFNULL accepts exactly two arguments and returns the first if it’s not NULL, otherwise the second:

IFNULL(expression1, expression2)

Here’s a direct comparison:

-- These are functionally equivalent
SELECT IFNULL(phone, 'Not provided') FROM users;
SELECT COALESCE(phone, 'Not provided') FROM users;

-- COALESCE handles multiple arguments, IFNULL doesn't
SELECT COALESCE(discount_price, sale_price, regular_price) FROM products;

-- This would require nesting with IFNULL
SELECT IFNULL(discount_price, IFNULL(sale_price, regular_price)) FROM products;

When to use IFNULL: For simple two-argument cases, IFNULL is slightly more explicit about intent. Some developers prefer it for basic NULL checks because the name clearly states the purpose.

When to use COALESCE: For multiple fallback options, COALESCE is cleaner and more readable. It’s also standard SQL (IFNULL is SQLite-specific), making your queries more portable across database systems.

My recommendation: use COALESCE by default. It handles all cases IFNULL does, plus more complex scenarios, and it’s the standard SQL approach.

Practical Applications

COALESCE solves real-world problems across various domains. Here are scenarios where it’s indispensable.

Data Migration and Cleanup: When migrating data from multiple sources, COALESCE helps consolidate information:

-- Merging customer data from two systems
SELECT 
    customer_id,
    COALESCE(new_system.email, legacy_system.email) AS email,
    COALESCE(new_system.phone, legacy_system.phone) AS phone
FROM new_system
LEFT JOIN legacy_system USING (customer_id);

Address Handling: E-commerce applications often need to handle shipping addresses that default to billing addresses:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    billing_address TEXT NOT NULL,
    billing_city TEXT NOT NULL,
    billing_zip TEXT NOT NULL,
    shipping_address TEXT,
    shipping_city TEXT,
    shipping_zip TEXT
);

-- Get the address to ship to
SELECT 
    id,
    COALESCE(shipping_address, billing_address) AS ship_to_address,
    COALESCE(shipping_city, billing_city) AS ship_to_city,
    COALESCE(shipping_zip, billing_zip) AS ship_to_zip
FROM orders;

This pattern is extremely common and eliminates the need for application-level logic to determine which address to use.

Configuration and Settings: Applications with hierarchical configuration (user settings override defaults) benefit from COALESCE:

SELECT 
    user_id,
    COALESCE(user_settings.theme, system_defaults.theme, 'light') AS theme,
    COALESCE(user_settings.language, system_defaults.language, 'en') AS language
FROM users
LEFT JOIN user_settings USING (user_id)
CROSS JOIN system_defaults;

Common Pitfalls and Best Practices

Empty Strings Are Not NULL: This is the most common mistake with COALESCE. In SQLite, an empty string (’’) is a valid value, not NULL:

CREATE TABLE test (value TEXT);
INSERT INTO test VALUES (NULL), (''), ('actual value');

-- COALESCE doesn't replace empty strings
SELECT COALESCE(value, 'default') FROM test;
-- Returns: 'default', '', 'actual value'

-- To handle both NULL and empty strings
SELECT COALESCE(NULLIF(value, ''), 'default') FROM test;
-- Returns: 'default', 'default', 'actual value'

The NULLIF function converts empty strings to NULL, allowing COALESCE to treat them as missing values. This pattern is essential when working with data imported from CSV files or web forms where empty strings are common.

Type Coercion Considerations: COALESCE returns the data type of the first non-NULL argument. Be mindful when mixing types:

-- This works but may produce unexpected results
SELECT COALESCE(NULL, 42, 'fallback');  -- Returns: 42

-- Be explicit about types when necessary
SELECT COALESCE(NULL, CAST(42 AS TEXT), 'fallback');  -- Returns: '42'

Performance with Complex Expressions: COALESCE evaluates arguments left-to-right and stops at the first non-NULL value. Put simpler, more likely non-NULL expressions first:

-- Inefficient: expensive subquery evaluated first
SELECT COALESCE(
    (SELECT complex_calculation FROM heavy_table WHERE ...),
    simple_column,
    'default'
) FROM main_table;

-- Better: check simple column first
SELECT COALESCE(
    simple_column,
    (SELECT complex_calculation FROM heavy_table WHERE ...),
    'default'
) FROM main_table;

Avoid Overuse: Don’t use COALESCE where NOT NULL constraints or default values would be more appropriate. If a column should never be NULL in your application logic, enforce that at the schema level:

-- Better schema design
CREATE TABLE users (
    phone TEXT NOT NULL DEFAULT 'Not provided'
);

-- Than relying on COALESCE everywhere
CREATE TABLE users (
    phone TEXT
);
SELECT COALESCE(phone, 'Not provided') FROM users;  -- Repeated in every query

COALESCE is a powerful tool for handling NULL values in SQLite, but it’s most effective when combined with proper schema design and data validation. Use it to handle legitimately optional data and to establish clear fallback hierarchies, not as a band-aid for poor data modeling.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.