Categories:

Conditional expression functions

NVL

If expr1 is NULL, returns expr2, otherwise returns expr1.

Aliases:

IFNULL

Syntax

NVL( <expr1> , <expr2> )
Copy

Arguments

expr1

A general expression.

expr2

A general expression.

Usage notes

  • If possible, pass in arguments of the same type. Avoid passing in arguments of different types.

  • If one of the arguments is a number, the function coerces non-numeric string arguments (e.g. 'a string') and string arguments that are not constants to the type NUMBER(18,5).

    For numeric string arguments that are not constants, if NUMBER(18,5) is not sufficient to represent the numeric value, you should cast the argument to a type that can represent the value.

  • Either expression can include a SELECT statement containing set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General usage notes in the Set operators topic.

Collation details

Returns

Returns the data type of the returned expression.

If both expressions are NULL, returns NULL.

Examples

Create a table that contains contact information for suppliers:

CREATE TABLE IF NOT EXISTS suppliers (
  supplier_id INT PRIMARY KEY,
  supplier_name VARCHAR(30),
  phone_region_1 VARCHAR(15),
  phone_region_2 VARCHAR(15));
Copy

The table contains the phone number for each supplier in two different regions. The phone number can be NULL for a region.

Insert values into the table:

INSERT INTO suppliers(supplier_id, supplier_name, phone_region_1, phone_region_2)
  VALUES(1, 'Company_ABC', NULL, '555-01111'),
        (2, 'Company_DEF', '555-01222', NULL),
        (3, 'Company_HIJ', '555-01333', '555-01444'),
        (4, 'Company_KLM', NULL, NULL);
Copy

The following SELECT statement uses the NVL function to retrieve the phone_region_1 and phone_region_2 values.

This example shows the following results for the NVL function:

  • The IF_REGION_1_NULL column contains the value in phone_region_1 or, if that value is NULL, the value in phone_region_2.

  • The IF_REGION_2_NULL column contains the value in phone_region_2 or, if that value is NULL, the value in phone_region_1.

  • If both phone_region_1 and phone_region_2 are NULL, the function returns NULL.

SELECT supplier_id,
       supplier_name,
       phone_region_1,
       phone_region_2,
       NVL(phone_region_1, phone_region_2) IF_REGION_1_NULL,
       NVL(phone_region_2, phone_region_1) IF_REGION_2_NULL
  FROM suppliers
  ORDER BY supplier_id;
Copy
+-------------+---------------+----------------+----------------+------------------+------------------+
| SUPPLIER_ID | SUPPLIER_NAME | PHONE_REGION_1 | PHONE_REGION_2 | IF_REGION_1_NULL | IF_REGION_2_NULL |
|-------------+---------------+----------------+----------------+------------------+------------------|
|           1 | Company_ABC   | NULL           | 555-01111      | 555-01111        | 555-01111        |
|           2 | Company_DEF   | 555-01222      | NULL           | 555-01222        | 555-01222        |
|           3 | Company_HIJ   | 555-01333      | 555-01444      | 555-01333        | 555-01444        |
|           4 | Company_KLM   | NULL           | NULL           | NULL             | NULL             |
+-------------+---------------+----------------+----------------+------------------+------------------+
Language: English