SQL Null Functions

In SQL, NULL represents missing or unknown data. When dealing with NULL values, it is often important to replace them with more meaningful data. SQL provides several functions to handle NULL values across different databases. The most common ones are IFNULL (), ISNULL (), COALESCE (), and NVL ().

SQL Null Functions Syntax

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

• IFNULL(expression, replacement_value) – Replaces NULL with a specified replacement value.
• ISNULL(expression, replacement_value) – Similar to IFNULL(), but specific to SQL Server.
• COALESCE(expression1, expression2, ..., expressionN) – Returns the first non-NULL value in a list of expressions.
• NVL(expression, replacement_value) – Replaces NULL with a replacement value, specific to Oracle databases.

SQL Null Functions Example Solutions

SQL Null Functions Using IFNULL() Example

This query checks for null values in the last_name column of the Patients table and replaces them with ‘Unknown’

SELECT patient_id, IFNULL(last_name, 'Unknown') AS last_name
FROM Patients;

SQL Null Functions Using COALESCE() Example

This query checks multiple columns in the Doctors table and returns the first non-null value.

SELECT doctor_id, COALESCE(first_name, 'Unknown', last_name) AS name
FROM Doctors;

SQL Null Functions MySQL

MySQL supports functions like IFNULL() and COALESCE() to handle nulls. You can use them as follows, In this query, if doctor_specialty is null, it will show ‘General’ instead.:

SELECT IFNULL(doctor_specialty, 'General') AS specialization FROM Doctors;

SQL Null Functions SQL Server

In SQL Server, ISNULL() can be used to replace nulls, This query will replace null values in the last_name column with ‘Unknown’.:

SELECT ISNULL(last_name, 'Unknown') AS last_name FROM Patients;

SQL Null Functions MS Access

In MS Access, you can use Nz(), which is similar to IFNULL(), This query will replace null last_name values with ‘Unknown’ in MS Access. :

SELECT Nz(last_name, 'Unknown') AS last_name FROM Patients;

SQL Null Functions Oracle

In Oracle, NVL() is used similarly to IFNULL(), This Oracle query replaces null values in doctor_specialty with ‘General’.:

SELECT NVL(doctor_specialty, 'General') AS specialization FROM Doctors;

SQL Null Functions Labs