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.
Also Read, LIMIT in SQL
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 VALUES ( 1001, "Sonal", 24, "9 Kaveri Apartments" ); INSERT INTO EMPLOYEE VALUES ( 1002, "Rohit", NULL, "13 Lakshmi Nagar" ); INSERT INTO EMPLOYEE VALUES ( 1003, "Nidhi", NULL, NULL ); |
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 |
117+ registered 