May 16, 2021 SQLite
The NULL of SQLite is an item that represents a missing value. An NULL value in the table is a value that appears blank in a field.
A field with an NULL value is a field without a value. It is important to understand that NULL values are different from zero values or fields that contain spaces.
The basic syntax for using NULL when creating tables is as follows:
SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
Here, the NOT NULL means that the column always accepts explicit values for a given data type. T here are two columns here that we don't use NOT NULL, which means that these two columns can be NULL.
Fields with NULL values can be kept blank when records are created.
NULL values cause problems when selecting data because when you compare an unknown value to another, the results are always unknown and are not included in the final results. Assuming there is a table below, THE COMPANY's record looks like this:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
Let's use the UPDATE statement to set some values that allow empty values to be NULL, as follows:
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
The records in the COMPANY table now look like this:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 7 James 24
Next, let's look at the usage of the IS NOT NULL operator, which lists all salary records that are not NULL:
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
The SQLite statement above produces the following result:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
Here's how the IS NULL operator is used, and all SALARY records are listed as NULL:
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
The SQLite statement above produces the following result:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24