SQLite Null (Is Null) Values

  Total Views : 39
  Comments : 0
 
  Keywords : SQLite get null or empty string value rows or records with example, SQLite null value comparison using is null condition with example, SQLite check for empty string using is null example

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.

SQLite Null Values

Generally, all relational databases will support a special value called NULL and it is used to represent missing information. If table have Null value, then it will display as blank.

 

The Null value represents represent absence of value or empty or no value. By using NULL keyword, we can represent NULL or empty string values.

Example of SQLite Is Null Condition

For illustrating NULL value in SQLite, we take the example of 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 Person table. So we will execute the following SQLite query.

 

sqliteSELECT 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 person whose phone number is NULL then we have to use IS operator with NULL as follows:

 

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

Example of SQLite Is Not Null Condition

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.

 

sqliteSELECT 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

Following are the some of sample SQLite examples using NULL.

 

sqliteSELECT NULL = NULL;

 

NULL

 

sqliteSELECT NULL OR NULL;

 

NULL

 

sqliteSELECT NULL AND NULL;

 

NULL

 

sqliteSELECT NOT NULL;

 

NULL

 

sqliteSELECT 6589856 - 5487 *NULL;

 

NULL

In SQLite it is important to note that how count(*) and count(column) will work when they are handling NULL values. 

 

Count(*) - It only count records regardless of any NULL values in column and return the count. 

 

Count(column) - It returns only the count of NOT-NULL values. It will not count the column with NULL value.

 

Following is the truth table that shows how NULL value will react with Logical expressions i.e. with AND & OR operator.

 

ABA AND BA 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 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 table.  Following is the example of using NULL value in SQLite while creating table called STUDENT.

 

CREATE TABLE STUDENT

(ID INTEGER PRIMARY KEY,

NAME TEXT NOT NULL,

EMAIL TEXT,

MARKS FLOAT);

Here we declared NAME column with NOT NULL constraint that signifies NAME column will not accept NULL as value.

 

If we tried to insert NULL value in NAME column, then it will throw error like as shown below.

 

sqlite>insert into student (ID,NAME,EMAIL,MARKS)

   ...>VALUES(5,NULL,'abc@xyz.in',55);

 

ErrorNOT NULL constraint failed: STUDENT.NAME

This is how we can use NULL values in SQLite statements based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User