Null

Learn to use NULL with Kuika.

NULL literally means nothing in SQL, as well as programming in general.

In SQL, it is better to consider it as "the absence of any value". It is important to know that is different from seemingly empty values, such as the empty string '' or the number 0, neither of which are actually NULL. It is also important to be careful not to write NULL in quotes, like 'NULL', which will be considered as text, but is not NULL and can cause errors and incorrect data sets.

Filtering for NULL in queries

The syntax for filtering for NULL (where nothing exists) in WHERE blocks differs from filtering for specific values.

SELECT * FROM Employees WHERE Managerid IS NULL;
SELECT * FROM Employees WHERE Managerid IS NOT NULL;

Note that because NULL is not equal to anything (including itself), using equality operators = NULL or <> NULL (or != NULL) will have a value of UNKNOWN which will be rejected by WHERE. WHERE filters all rows conditioned FALSE or UKNOWN and keeps only rows that the condition is TRUE.

Nullable columns in tables

When you’re creating a table it is possible to declare a column to be nullable or non-nullable

CREATE TABLE My Table
(
    MyCol1 INT NOT NULL, --non nullable
    MyCol2 INT NULL      -- nullable
( ;

Normally every column is nullable except when it is explicitly set NOT NULL.

Attempting to assign NULL to a non-nullable column will result in an error.

INSERT INTO MyTable (MyCol1, MyCol2 ) VALUES (1, NULL) ; --works fine
INSERT INTO Mytable (MyCol1, MyCol2) VALUES (NULL, 2) ;
    --cannot insert
    --the value NULL into column 'MyCol1' , table 'MyTable';
    --column does not allow nulls. INSERT fails.

‍Updating fields to NULL

‍Changing a field to NULL works exactly like it would with any other value:

UPDATE Employees
SET ManagerId = NULL
WHERE Id = 4

‍Inserting rows with NULL fields ‍

E.g: Inserting an employee without a phone number and without his/her manager into the Employees example table:

INSERT INTO Employees
( Id, FName, LName, PhoneNumber, Managerid, Departmaentld, Salary, HireDate)
VALUES
(5, 'Jane', 'Doe', NULL, NULL, 2, 800, '2016-07-22');