SQLite IS NULL Condition

“SQLite Database provides different ways for constructing, deleting, and running SQL statements and other database management operations. In general, all database systems may contain the NULL value, which will be applied to indicate missing information. If any column of the table contains the NULL, that will appear blank. The Null value denotes the lack of a value or unfilled or no valuation. We would specify NULL or unfilled string elements with the NULL term.

In this article, we will discuss the utilization of SQLite IS NULL condition to evaluate for null or blank string entries in detail.

For executing the commands of SQLite, we installed the “BD Browser for SQLite compiler.” The database file is stored in any folder of the computer with the “SQLite databases files’ type.”

Creation of Table

After creating the database, we have to create the table by using CREATE query. Here we create a table of “Admin.” This table has several attributes. The values of these attributes are represented in columns. The names of the columns are Id, Name, Gender, Age, and City. The attribute Id is the primary key of table “Admin” having INTEGER data type, Name, Gender, and City has a TEXT data type, and the Age attribute has a NUMERIC data type.

After implementing the above query, we have gotten this type of output. This shows we have successfully created the table named “Admin.”

Insertion of Data

To insert the data in the columns of the given table, we have utilized the INSERT query. We have added data of different admins in the table. We have provided the value of “City” for two admins as “NULL.” Similarly, we have specified the value of “Age” of the admin to “NULL.”

When we run the above-mentioned query, it gives this type of outcome, which shows the query of INSERT is executed without any error.

Use SELECT Query

After inserting the values into the table, we have to employ the SELECT query of SQLite. This query is being used to acquire the entire data of all required table columns. If we want to get the data of some specific columns, we will specify those columns’ names in the SELECT query.

Here we have been using the * symbol in the SELECT query. So we obtain the data of all the columns of the table “Admin.” This table has five columns, including “Id,” “Name,” “Gender,” “Age,” and “City.” There is a record of five different people working as Admin.

Use IS NULL Condition

NULL is unique. It denotes that a bit of data is either unidentified or irrelevant. NULL is not the same as the integer zero, a blank string, or anything else.

We can utilize the “IS NULL” condition to check the “NULL” value of the attributes of the table. “IS NULL” is employed in the WHERE clause of the query. In this instance, we applied the IS NULL condition on the column “Age.” It means we want to retrieve the data of those admins whose Age IS NULL.

When we run this query by tapping the “SHIFT” and “F5” from the keyboard, we get this type of resultant table.

Use DELETE Query

Whenever we want to remove any record from the table, we will use the DELETE query. Here we are going to delete all records of that admin whose age is not specified. So we utilized IS NULL condition.

Now we will execute this query to check out the result. We successfully deleted the row where age IS NULL.

After deleting the record of the admin whose age is unknown, now we show the data of the table “Admin” by using the SELECT query.

As this output shows that the table contains all the rows except that one where the value of the Age of the Admin IS NULL.

In this case, we are going to apply the IS NULL condition on the column “City” of the table “Admin.” Here, when we have used the SELECT query, we have provided the names of those columns which we want to retrieve from the table.

We don’t get all the columns of the table where City IS NULL; we just acquire the Id, Name, and City of the admins.

Now we want to delete the record of the admins who belong to the unknown city. There are two admins whose city IS NULL.

The DELETE query effectively erases the record from the table “Admin.”

Once removing the data of those admins whose city IS NULL, we have employed the SELECT query once again to represent the data of the table “Admin.”

The successful execution of the above-mentioned query is shown below.

Use NOT NULL

Just like the NULL condition, we can utilize the NOT NULL condition on the column of the table. In this query, we apply the NOT NULL condition on the column “Gender.”

The SELECT query returns the record (id, name, gender, and city) of those admins where gender IS NOT NULL.

Conclusion

With the help of different queries, this article demonstrates the use of IS NULL condition in SQLite queries to observe if the required value is NULL or not. We can utilize the IS NULL condition along with the DELETE clause and WHERE clause of the SELECT statement. We also explained how to apply the NOT NULL condition in the SELECT query of SQLite. If the defined value of the column of the table is NULL, the query returns TRUE; otherwise, it returns FALSE.