SQLite IS NULL

First of all, let us know about the word NULL, what does it mean? NULL is nothing but the value or information that is unknown. This NULL value is filled inside the table when no value is given to the particular row or column. The NULL value is always not equal to ZERO.

For example, to understand it in a better way, let us consider a products table and products in the database may have manufacture date and some do not in the same way sometimes there will be no product ID too, these are called NULL values. We use the SQLite IS NULL operator in the WHERE clause.

NULL is the word that is used to represent the missing value.

Syntax

select column1, column2, columnN

from Table Name

Where columname IS NULL;

Now let us try to create the Company table

Syntax

Following is the basic syntax of using NULL while creating a table.

CREATE TABLE Company (
    ID    INTEGER ,
    Name    TEXT ,
    Age    INTEGER ,
    Address    TEXT,
    Salary    INTEGER,
    PRIMARY KEY("ID")
);

In the above syntax, we are using the create statement to create a table and if we use NOT NULL beside the data type of the column then it does not accept the NULL values. However, I am not using NOT NULL because we need some NULL value rows in order to understand the topic.

Now insert the data into the table

Query:

Insert into company(ID, Name, Age, Address, Salary)
values(1,'Jones', 30, 'Dallas',40000),
(2, 'Mark',NULL ,'New York', 50000),
(3, 'Jessy', 35, 'Verginia', NuLL ),
(4,NuLL,NULL,'Missippi', 60000),
(5,'Rosey',NuLL,NuLL,45000);

We have filled the empty rows with the NULL values and now the table looks like this:

Output:

The values with the NULL values are the rows that do not contain any data in them.

The NULL values affect the total result because when we compare the NULL value with the other value in the table it returns the null value only and then that is not considered in the final results.

SQLite is a server-less database engine and it is written in c programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing the SQLite is to escape from using the complex database engines like MYSQL.etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, web browsers, and many more. It is written simply so that it can be embedded into other applications.

In this article we will be learning about the SQLite IS NULL operator and by the end of this article, you will get a basic understanding of how the SQLite IS NULL operator works exactly. By the way, we will also be seeing the SQLite IS NOT NULL operator along with the SUM function in the SQLite.