SQLite IS NULL

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.

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:

company table

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.

Examples of SQLITE IS NULL

Now let’s try to bring out some rows with NULL values by using their field names. Now let us display the row where in the salary field there is a null value in the company table.

select * from Company
where salary is null;

Output:

SALARY IS NULL

And one record is returned as there is no value in the salary column. In the same way, let us try to fetch the rows that have the AGE field as Null.

select * from Company
where AGE IS NULL;

Output:

AGE IS NULL

Here 3 rows are retrieved as the column of AGE has a null value in three rows.

SQLITE IS NOT NULL

IS NOT NULL means that no missing data and now let us try to fetch the rows that have no null value for the specified field. IS NOT NULL operator fetches the rows that only have the not null values which means simply the rows that have the data for the specified column.

select column1, column2, columnN
from Table Name
Where columname IS NOT NULL;

Explanation

we need to specify the field that you want to fetch followed by the table name from which you are going to fetch data and followed by the Where clause and then IS NOT NULL operator.

Example

select Address
from Company
where Address IS NOT NULL;

Output:

ADDRESS IS NOT NULL

As you can see only 4 columns are retrieved as they have the data in the address field, you can also see there are 5 rows in our company table but as one row has the NULL value in the Address field it is not fetched.

SQLite SUM Function

It is an aggregate function and returns the summation of whatever the expression that you specify. It uses the ALL clause by default and it adds the values, even the duplicates too. But if you want to count only the unique values then you need to use the DISTINCT clause.

• If all the non-null values are integers then only the result of the sum function will be an integer and if any of the input is null or not an integer then the output would be a floating point.
• The sum value will be NULL if all the inputs are NULL.

Syntax

SELECT sum(aggregate_expression)
FROM table
[WHERE conditions];

Explanation

select statement used to display the data followed by SUM function, inside the sum write the column name that you want to apply the summation on it followed by the tablename from which you are going to fetch data and where condition based on that conditions if the condition is true then the output is displayed if false nothing is displayed.

Example

select sum(salary) as totalSalary
from company
where salary>20000;

Here we are going to perform a summation the salary column and as we can in our table there is one NULL value and it is not going to be considered in the summation, only the four columns are added, and the result we are going to fetch it as a totalsalary i.e we are using the alias name for the column.

Output:

SUM OF SALARY

Using Formula

Here in the summation, there need not to be only one expression, there can be multiple expressions too that you can specify. Let us try to calculate the commission.

select sum(salary/12)
from Company
where salary>45000;

As you can see we have used 2 expressions in the sum function and followed by the table name where condition that to consider only the salaries that are greater than 45000 and the fetched data is as shown below.
Output:

Output

As we have not chosen any alias name the column is fetched the same as the expression name.

Conclusion

SQLite IS NULL function is used to display the null values whereas IS NOT NULL is used to fetch the data that do not have any null values from the table according to the specified query. SQLite SUM function is used to perform the summation on the specified fields and it considers only the null values for the summation and fetches the data accordingly.