NULL Values

Pakhi Garg
Last Updated: May 13, 2022

Introduction

In a database, most of you have noticed that there are some records in a table that do not have values or data for all the fields. These fields appear blank. So what is the value present in these empty fields called? The value present in these empty fields is called NULL value.

In this article, we will discuss what NULL values are. We will also go through the example.

What are NULL values?

A NULL value is a special value in SQL which is used to represent the value of the fields that do not have any value. SQL places a NULL value in those fields if the user has not defined any value to a field while performing INSERT operations.

 

Source: Meme Generator

Reasons for keeping NULL values

There are a few reasons for which you can keep the value of a field as NULL:

  • If the value of the field is unknown.

While inserting records, it may happen that the value of a field exists but is not known at the moment. So, in these cases, the value can be kept NULL.

  • If the value does not exist.

While inserting records, it may happen that the value of a field is not available for some records or may be available in future. So, in these cases, the value can be kept NULL.

  • If the field is not applicable.

While inserting records, it may happen that a field does not apply to some records of the table. So, in these cases, the value of that field can be kept NULL for those records.

Important points to remember

  • It is important to note that a NULL value is not the same as a ZERO value in case the data type of field is number, and it is also not the same as spaces in case the data type of field is character.
  • We can insert NULL values in any field of the table irrespective of its data type.
  • If we perform the SELECT operation, then the NULL value present in a field will be displayed as NULL.
  • SQL will ignore the UNIQUE, FOREIGN KEY and CHECK constraints if any field has a NULL value.

 

Source: Reddit

How to insert and check NULL values in a field?

Inserting NULL value 

A user can also insert a NULL value in a field using the NULL keyword.

For example, there is a table EMPLOYEE with the fields ID, NAME, AGE and ADDRESS. So the following query will insert three records in the table; one having all the values and two having NULL values in the AGE and ADDRESS field.

INSERT INTO EMPLOYEE VALUES1001"Sonal"24"9 Kaveri Apartments" );
INSERT INTO EMPLOYEE VALUES1002"Rohit"NULL"13 Lakshmi Nagar" );
INSERT INTO EMPLOYEE VALUES1003"Nidhi"NULLNULL );

 

The output of the above query will be-

ID

NAME

AGE

ADDRESS

1001

Sonal

24

9 Kaveri Apartments

1002

Rohit

null

13 Lakshmi Nagar

1003

Nidhi

null

null

Checking NULL values

SQL queries can also be used to determine whether the value of a field is NULL or not. To compare the value of a field to NULL, we can use IS NULL or IS NOT NULL.

We will now discuss IS NULL and IS NOT NULL in detail.

IS NULL

IS NULL is an operator in SQL which is used to check if the value of a field is NULL or not. It returns TRUE if the value is NULL; otherwise, it returns FALSE. Based on the returned value, the operator filters the result set. IS NULL is always used with SELECT statements.

Syntax of IS NULL

Below is the syntax of IS NULL-

SELECT column(s) FROM table_name WHERE column IS NULL;

 

We will now discuss an example of IS NULL to make it clearer.

Example of IS NULL

Let us consider the same EMPLOYEE table we defined above with some more records.

ID

NAME

AGE

ADDRESS

1001

Sonal

24

9 Kaveri Apartments

1002

Rohit

null

13 Lakshmi Nagar

1003

Nidhi

null

null

1004

Payal

29

10/6 CP

1005

Raghav

31

null

The following query will fetch the name of the employees having age NULL.

SELECT NAME FROM EMPLOYEE WHERE AGE IS NULL;

 

The output of the above query will be-

NAME

Rohit

Nidhi

 

Let's consider one more query.

The following query will fetch the name of the employees having both age and address NULL.

SELECT NAME FROM EMPLOYEE WHERE AGE IS NULL AND ADDRESS IS NULL;

 

The output of the above query will be-

NAME

Nidhi


IS NOT NULL

IS NOT NULL is an operator in SQL which is used to check if the value of a field is NOT NULL or not. It returns TRUE if the value is NOT NULL; otherwise, it returns FALSE. Based on the returned value, the operator filters the result set. IS NOT NULL is always used with SELECT statements.

Syntax of IS NOT NULL

Below is the syntax of IS NOT NULL-

SELECT column(s) FROM table_name WHERE column IS NOT NULL;

 

We will now discuss an example of IS NOT NULL to make it more clear.

Example of IS NOT NULL

Let us consider the same EMPLOYEE table. 

The following query will fetch the name of the employees having age NOT NULL.

SELECT NAME FROM EMPLOYEE WHERE AGE IS NOT NULL;

 

The output of the above query will be

NAME

Sonal

Payal

Raghav

 

Let's consider one more query.

The following query will fetch the name of the employees having both age and address NOT NULL.

SELECT NAME FROM EMPLOYEE WHERE AGE IS NOT NULL AND ADDRESS IS NOT NULL;

 

The output of the above query will be-

NAME

Sonal

Payal

 

Frequently asked questions

  1. What is a NULL value used for?
    Ans. A NULL value is used in the field of a table when the actual value is unknown, does not exist or the field is not applicable.
     
  2. Can we make a primary key and foreign key NULL?
    Ans. No, we cannot make a primary key NULL, but yes, we can make a foreign key NULL.
     
  3. Is NULL value equivalent to zero?
    Ans. No, a NULL value is not equivalent to zero. A NULL value means no value, whereas zero means number zero.
     
  4. How to check NULL values?
    Ans. In a table, we can check whether a field of a record has NULL value or not using IS NULL and IS NOT NULL operator.

Key Takeaways

In this article, we have briefly discussed what is a NULL value. Reasons for keeping NULL value, inserting NULL values in a table, and check NULL values in a table- using IS NULL and IS NOT NULL. IS NULL returns TRUE if the value in a field is NULL and IS NOT NULL returns TRUE if the value in a field is NOT NULL. We went thoroughly through the NULL value concept and discussed various examples.

Reader, don’t stop here. Start your DBMS journey with the DBMS course. Solve SQL problems here.

Happy Learning!

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think