What is “IS NULL” query in MySQL

The ‘IS NULL” query in the MySQL database shows the data which is either missing or is unknown to DMS. A NULL value is different as it has no value, it is neither equal to zero integer or to an empty set. Null is a state, not a value, if we compare the NULL value to any other NULL value, the result will always be NULL because it’s unknown itself. A “IS NULL” query is used when data is missing or unknown for example we make a list of phone directories, if any person’s phone number is unknown then “IS NULL” will extract it and the number can later be added to complete the directory.

In this article, we are going to understand what the “IS NULL” query is and how it works with some examples.

What is a IS NULL query

A IS NULL query is used to fetch out the data of the table which is unknown or missing, when we are creating a table we have to inform the table whether to accept the NULL values by using “NULL query” or not by using the “NOT NULL”. If we select the constraint “NOT NULL” then it will not accept NULL values for example we create a table of a phone directory where we make columns of “Name”, “Phone number” and “Email”, we select name as NULL and phone number as “Not Null”.

CREATE TABLE phone_directory_1 (name VARCHAR(50) NULL, phone_number INT(50) NOT NULL, email VARCHAR(50));

Now we will insert values and leave the values empty of Name and Phone_number.

INSERT INTO phone_directory_1 VALUE (null,null,‘[email protected]’);

We can see that it generated the error of not accepting NULL value in the column “Phone_number” because it was initialized with the “NOT NULL” value. Now create another table with the values “NULL”.

CREATE TABLE phone_directory_2 (name VARCHAR(50) NULL, phone_number INT(50) NULL, email VARCHAR(50));

Again insert the data in it like Null values in “name” and “phone_number” also some value in “email”.

INSERT INTO phone_directory_2 VALUE ( Null,Null,‘[email protected]’);

To view the table:

SELECT * FROM phone_directory_2;

Add one more entry to the table.

INSERT INTO phone_directory_2 VALUE (‘John’,12345,‘[email protected]’);

Again to view the modified table.

SELECT * FROM phone_directory_2;

Now we will extract the null values from the table by using the “IS NULL ” clause but before using that we will discuss the general syntax of using the “IS NULL ” clause.

SELECT column1,column2,... FROM [table_name] WHERE column IS NULL;

Following the general syntax, we can fetch out the NULL values from the Name column by executing the command.

SELECT name, phone_number,email FROM Phone_directory_new WHERE Name IS NULL;

From the output, we can see the value of the column where “name” is Null has been extracted. To understand it more clearly we edit a new entry where only the “Phone_number” column is null.

INSERT INTO phone_directory_2 VALUE (‘Alex’,Null,‘[email protected]’);

To view columns.

SELECT * FROM phone_directory_2;

We will extract all the NULL values from the Phone_number column using the “IS NULL” clause.

SELECT name, phone_number,email FROM phone_directory_2 WHERE phone_number IS NULL;

It displays all the null values of the column “Phone_number”. It has been cleared from the example above that the “IS NULL” clause is used to extract the null values from the column of the table.

Conclusion

Sometimes a user leaves the entry by mistake which is considered as the NULL value. To extract such null values and to re-edit them we use the “IS NULL” clause. In this article, we have discussed what is the difference in the creation of tables using NULL or NOT NULL clauses and also explained what is the “IS NULL” query and how we can use it to extract the null values from the columns of the table.

What is “IS NULL” query in MySQL

What is a IS NULL query

Conclusion

About the author

Hammad Zahid