ISNULLexpression

An ISNULLexpression, or an ISNULL expression, is an expression that uses the ISNULL function to substitute a non-null value for a null input. It is commonly used in SQL dialects that implement the ISNULL function, most notably Transact-SQL (SQL Server) and Sybase ASE.

Syntax and behavior

The typical syntax is ISNULL(expression, replacement). The function evaluates the first argument; if it is not NULL, that value is returned. If the first argument is NULL, the function returns the second argument (the replacement). The result’s data type is determined by the data types of the two arguments, with the replacement value converted as needed to fit the type of the first argument in many implementations.

Portability and alternatives

ISNULL is not part of the ANSI SQL standard, so its exact behavior and return type rules can vary between systems. In standard SQL, the COALESCE function provides similar, often more portable behavior and can accept more than two arguments (selecting the first non-NULL value). Other databases use NVL (Oracle) or IFNULL (MySQL) for analogous purposes.

Examples

- SELECT ISNULL(city, 'Unknown') FROM customers; replaces NULL city values with 'Unknown'.

- SELECT ISNULL(total_amount, 0) AS total FROM orders; yields 0 when total_amount is NULL.

Usage notes

ISNULL is useful for ensuring non-null outputs in expressions, computed columns, or query results. However, when used in predicates, it can affect index usability in some engines. For portability considerations, COALESCE is often preferred in multi-database environments. See also: COALESCE, NVL, IFNULL, IS NULL.