In SQL, NULL is a special marker used to indicate that a data value does not exist in the database.
The term “NULL” is used to represent a missing or unknown value. It is not the same as an empty string or a zero value.
The NULL value is often used to represent missing or incomplete data, or to indicate that the value of a field is not yet known.
SQL NULL functions are used to handle NULL values in the database.
These functions allow you to test for NULL values, replace NULL values with a default value, and perform calculations with NULL values.
In this tutorial, we will discuss the different types of SQL NULL functions, and provide examples of basic and advanced usage.
Why and When to Use SQL NULL Functions
There are several reasons why you might need to use SQL NULL functions:
Missing or incomplete data
When data is incomplete, NULL values can be used to represent missing information. For example, if a customer did not provide their phone number, the phone number field could be filled with a NULL value.
NULL values can be used to validate data. For example, if a field should contain a valid date, a NULL value can be used to indicate an invalid date.
SQL NULL functions can be used to manipulate NULL values in the database. For example, you can use the COALESCE function to replace NULL values with a default value.
SQL NULL Functions
IS NULL and IS NOT NULL
The IS NULL function is used to test whether a value is NULL or not. The syntax is as follows:
SELECT column_name FROM table_name WHERE column_name IS NULL;
The IS NOT NULL function is used to test whether a value is not NULL. The syntax is as follows:
SELECT column_name FROM table_name WHERE column_name IS NOT NULL;
SELECT * FROM users WHERE phone_number IS NULL;
This query selects all records from the “users” table where the “phone_number” column is NULL.
The COALESCE function is used to replace NULL values with a default value. The syntax is as follows:
COALESCE(value_1, value_2, ..., value_n, default_value)
The function returns the first non-NULL value in the list of values, or the default value if all values are NULL.
SELECT COALESCE(phone_number, 'N/A') AS phone_number FROM users;
This query selects all records from the “users” table and replaces NULL values in the “phone_number” column with the string “N/A”.
The NULLIF function is used to compare two expressions and return NULL if they are equal. The syntax is as follows:
The function returns NULL if the two expressions are equal, and returns the value of the first expression if they are not equal.
SELECT NULLIF(quantity, 0) AS adjusted_quantity FROM orders;
This query selects all records from the “orders” table and replaces 0 values in the “quantity” column with NULL.
CASE statement with NULL values:
SELECT name, CASE WHEN phone_number IS NULL THEN 'No phone number' ELSE phone_number END AS phone_number FROM users;
This query selects all records from the “users” table and uses a CASE statement to replace NULL values in the “phone_number” column with the string “No phone number”.