Latest web development tutorials

SQLite NULL value

SQLite TheNULL value is used to indicate a missing item.Table a NULL value is displayed in the field to a blank value.

Fields with a NULL value is a value field without. NULL values ​​and values ​​that contain spaces or zero field is different, and understand this is very important.

grammar

The basic syntax for usingNULL when you create a table 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, NOT NULL column indicates always accepted the given explicit value data type.There are two columns we do not use NOT NULL, which means that the two columns can not be NULL.

Fields with NULL values ​​in a record is created when you can leave it blank.

Examples

NULL value when selecting data can cause problems, because when the value by another unknown a comparison, the result is always the unknown, and will not be included in the final results. Suppose the following table, COMPANY records as follows: 

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 that allow nulls is NULL, as follows: 

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN (6,7);

Now, recording COMPANY table is as follows: 

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 us look at theIS NOT NULL operator usage, which is used to list all the SALARY is not NULL record:

sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

The above SQLite statement will produce the following results: 

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 is theIS NULL operator usage, lists all SALARY is NULL record:

sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

The above SQLite statement will produce the following results: 

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22
7 James 24