DbSchema Tutorial | SQL NULL VALUES

Publish on DbSchema Blog >>>

SQL, which stands for Structured Query Language, is a domain-specific language used for managing and querying relational databases. One of the important concepts in SQL is the NULL value. In this article, we will discuss in detail what NULL values are, how to handle them using different operators and functions, and the common mistakes and FAQs related to this topic.

What is NULL?

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is important to note that NULL is not the same as an empty string or a zero value. It is a distinct concept that represents missing or unknown data. In other words, NULL signifies the absence of any value.

IS NULL Operator

The IS NULL operator in SQL is used to check whether a column value is NULL or not.

Syntax

SELECT * FROM table_name WHERE column_name IS NULL;

Example

Let’s consider a sample database table, Students, as shown below:

StudentID	FirstName	LastName	Age
1	John	Doe	20
2	Jane	Doe	NULL
3	Sam	Smith	25

The SQL query to find the rows where the Age is NULL will be:

SELECT * FROM Students WHERE Age IS NULL;

Result:

StudentID	FirstName	LastName	Age
2	Jane	Doe	NULL

Explanation

The query selects all rows from the Students table where the Age is NULL. In this case, it returns the row of ‘Jane Doe’ because her age is marked as NULL.

IS NOT NULL Operator

The IS NOT NULL operator is used to check whether a column value is not NULL.

Syntax

SELECT * FROM table_name WHERE column_name IS NOT NULL;

Example

Using the same Students table, the SQL query to find the rows where the Age is not NULL will be:

SELECT * FROM Students WHERE Age IS NOT NULL;

Result:

StudentID	FirstName	LastName	Age
1	John	Doe	20
3	Sam	Smith	25

Explanation

The query selects all rows from the Students table where the Age is not NULL. In this case, it returns the rows of ‘John Doe’ and ‘Sam Smith’ because their ages are not marked as NULL.

Handling SQL NULL values with Functions

SQL provides various functions to handle NULL values. Two commonly used functions in MySQL are ISNULL() and IFNULL().

ISNULL() Function

The ISNULL() function in MySQL is used to check whether a column value is NULL. It returns 1 if the column value is NULL, otherwise 0.

Syntax

SELECT * FROM table_name WHERE ISNULL(column_name);

Example

Using the Students table, the SQL query to find the rows where the Age is NULL using the ISNULL() function will be:

SELECT * FROM Students WHERE ISNULL(Age);

Result:

StudentID	FirstName	LastName	Age
2	Jane	Doe	NULL

Explanation

The query selects all rows from the Students table where the Age is NULL using the ISNULL() function. In this case, it returns the row of ‘Jane Doe’ because her age is marked as NULL.

IFNULL() Function

The IFNULL() function in MySQL is used to replace NULL values with a specified value.

Syntax

SELECT column_name1, column_name2, ..., IFNULL(column_name, replacement_value) FROM table_name;

Example

Using the Students table, the SQL query to replace the NULL values in the Age column with ‘Unknown’ will be:

SELECT StudentID, FirstName, LastName, IFNULL(Age, 'Unknown') AS Age FROM Students;

Result:

StudentID	FirstName	LastName	Age
1	John	Doe	20
2	Jane	Doe	Unknown
3	Sam	Smith	25

Explanation

The query replaces any NULL values in the Age column with ‘Unknown’ and selects all rows from the Students table. As ‘Jane Doe’ has a NULL age, it is replaced with ‘Unknown’ in the result.

Difference between ‘IS NULL’ and ‘= NULL’

In SQL, it is a common mistake to use = NULL instead of IS NULL. The = operator is used for comparing values. However, NULL is not a value, but a marker for missing or unknown data. Therefore, IS NULL is the correct way to check for NULL.

Example

Using the Students table, let’s compare the results of = NULL and IS NULL.

SELECT * FROM Students WHERE Age = NULL;

Result:

No rows selected

SELECT * FROM Students WHERE Age IS NULL;

Result:

StudentID	FirstName	LastName	Age
2	Jane	Doe	NULL

Explanation

As you can see, the = NULL query does not return any rows, whereas the IS NULL query correctly identifies the row with a NULL Age. This is because the = operator cannot be used for checking NULL values, and the correct operator to use is IS NULL.

How to Count SQL NULL values in a column

To count the number of NULL values in a column, you can use the COUNT function along with the IS NULL operator.

Syntax

SELECT COUNT(*) FROM table_name WHERE column_name IS NULL;

Example

Using the Students table, the SQL query to count the number of NULL values in the Age column will be:

SELECT COUNT(*) FROM Students WHERE Age IS NULL;

Result:

COUNT(*)
1

Explanation

The query counts the number of rows in the Students table where the Age is NULL. In this case, it returns 1 because there is one row with a NULL age.

Common Mistakes

1. Using = NULL instead of IS NULL: As mentioned earlier, = NULL does not work as expected. Always use IS NULL to check for NULL values.

2. Treating NULL as an empty string or zero: NULL is a special marker and should not be confused with an empty string or zero.

FAQs

1. What is the difference between NULL and an empty string?

   • NULL indicates the absence of any value or unknown data, whereas an empty string is a known value that is simply empty.

2. Can a primary key be NULL?

   • No, a primary key cannot be NULL. A primary key is used to uniquely identify each row in a table, and therefore, it must have a value for each row.

3. Is NULL equal to NULL in SQL?

   • No, NULL is not equal to NULL in SQL. Since NULL signifies unknown or missing data, it cannot be compared to another NULL or any other value.

Practice Questions

1. Write a SQL query to select all rows from a table where a specific column is not NULL.

2. Write a SQL query to replace all NULL values in a specific column with a default value.

3. Write a SQL query to count the number of non-NULL values in a specific column.

4. Write a SQL query to select all rows from a table where two specific columns are NULL.

To truly master the NULL VALUES in SQL, regular practice and real-world application are essential. This guide offers a comprehensive overview, but hands-on experience will solidify your understanding.

_Happy Querying !!_