Here we will learn sqlite null and is null conditions with example and how to use sqlite null and is null to check for null or empty string values with example.
Generally, all relational databases will support a special value called NULL and it is used to represent missing information. If table has Null value, then it will display as blank.
The Null value represents represent the absence of a value or empty or no value. By using the NULL keyword, we can represent NULL or empty string values.
For illustrating NULL value in SQLite, we take the example of a Person table which is having records like as shown below.
sqlite>SELECT * FROM Person;
SSID first_name last_name email phone_no city
---------- ---------- ---------- --------------- ---------- ----------
1 Vinay Jariwala vinay@gmail.com 898545 Vapi
2 Shweta Jariwala shweta@gmail.com Vapi
3 Sonal Menpara Sonal@gmail.com 84697 Surat
4 Jagruti Viras jagu@gmail.com 656454 Daman
5 Yamini Patel rani@gmail.com 98788 Mumbai
6 Yamini Shah yamini@gmail.com Baroda
Now, we will try to get persons whose phone number is NULL from the Person table. So we will execute the following SQLite query.
sqlite> SELECT * FROM Person WHERE phone_no = NULL;
When we run above SQLite query it won’t return any rows because here we are comparing phone_no = NULL. Here NULL is not TRUE or FALSE so no rows will be returned by the query in its current form. So, to get records of a person whose phone number is NULL then we have to use IS operator with NULL as follows:
sqlite> SELECT * FROM Person WHERE phone_no IS NULL;
SSID first_name last_name email phone_no city
---------- ---------- ---------- ---------------- ---------- ---------
2 Shweta Jariwala shweta@gmail.com Vapi
6 Yamini Shah yamini@gmail.com Baroda
In SQLite, the IS operator properly checks for a NULL and returns true records if it finds.
In SQLite, if you want to get values that are not NULL, then use IS NOT NULL. Following is the example of using SQLite Is Not NULL to get rows which are not containing any NULL values.
sqlite> SELECT * FROM Person WHERE phone_no IS NOT NULL;
SSID first_name last_name email phone_no city
---------- ---------- ---------- --------------- ---------- ----------
1 Vinay Jariwala vinay@gmail.com 898545 Vapi
3 Sonal Menpara Sonal@gmail.com 84697 Surat
4 Jagruti Viras jagu@gmail.com 656454 Daman
5 Yamini Patel rani@gmail.com 98788 Mumbai
The following are the some of sample SQLite examples using NULL.
sqlite> SELECT NULL = NULL;
NULL
sqlite> SELECT NULL OR NULL;
NULL
sqlite> SELECT NULL AND NULL;
NULL
sqlite> SELECT NOT NULL;
NULL
sqlite> SELECT 6589856 - 5487 *NULL;
NULL
In SQLite, it is important to note that how to count(*) and count(column) will work when they are handling NULL values.
Count(*) - It only count records regardless of any NULL values in a column and return the count.
Count(column) - It returns only the count of NOT-NULL values. It will not count the column with a NULL value.
Following is the truth table that shows how NULL value will react with Logical expressions i.e. with AND & OR operator.
| A | B | A AND B | A OR B |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | NULL | NULL | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL | NULL |
In SQLite we have a function called COALESCE to deal with NUL values. The SQLite COALESCE function takes a list of values and returns the first non-NULL in the list. We will learn more about COALESCE function in later topics.
Generally, we can use NULL value while creating a table. Following is the example of using the NULL value in SQLite while creating a table called STUDENT.
CREATE TABLE STUDENT
(ID INTEGER PRIMARY KEY,
NAME TEXT NOT NULL,
EMAIL TEXT,
MARKS FLOAT);
Here we declared the NAME column with a NOT NULL constraint that signifies the NAME column will not accept NULL as value.
If we tried to insert a NULL value in NAME column, then it will throw an error like as shown below.
sqlite>insert into student (ID,NAME,EMAIL,MARKS)
...>VALUES(5,NULL,'abc@xyz.in',55);
Error: NOT NULL constraint failed: STUDENT.NAME
This is how we can use NULL values in SQLite statements based on our requirements.