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”.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-01.png" data-lazy- height="90" src="data:image/svg xml,” width=”904″>

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-02.png" data-lazy- height="104" src="data:image/svg xml,” width=”904″>

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”.

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-03.png" data-lazy- height="130" src="data:image/svg xml,” width=”904″>

To view the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-04.png" data-lazy- height="410" src="data:image/svg xml,” width=”904″>

Add one more entry to the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-05.png" data-lazy- height="116" src="data:image/svg xml,” width=”904″>

Again to view the modified table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-06.png" data-lazy- height="470" src="data:image/svg xml,” width=”904″>

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.

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-07.png" data-lazy- height="536" src="data:image/svg xml,” width=”904″>

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.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-08.png" data-lazy- height="116" src="data:image/svg xml,” width=”904″>

To view columns.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-09.png" data-lazy- height="524" src="data:image/svg xml,” width=”904″>

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/is-null-query-in-mysql-10.png" data-lazy- height="562" src="data:image/svg xml,” width=”904″>

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.