YouTip

# SQLite NULL Values SQLite's **NULL** is used to represent a missing value item. A NULL value in a table is a value that appears blank in the field. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different from a zero value or a field containing spaces. ## Syntax The basic syntax for using **NULL** when creating a table 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, **NOT NULL** indicates that the column always accepts explicit values of the given data type. There are two columns where we did not use NOT NULL, which means these two columns can be NULL. Fields with NULL values can be left empty when the record is created. ## Examples NULL values can cause problems when selecting data, because when comparing an unknown value with another value, the result is always unknown and is not included in the final results. Suppose we have the following table, COMPANY with the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 Let us use the UPDATE statement to set some nullable values to NULL, as shown below: sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7); Now, the COMPANY table will have the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 227 James 24 Next, let us see the usage of the **IS NOT NULL** operator, which is used to list all records where SALARY is not NULL: sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL; The above SQLite statement will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.0 Below is the usage of the **IS NULL** operator, which will list all records where SALARY is NULL: sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL; The above SQLite statement will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------6 Kim 227 James 24