SQLite - NULL Values

SQLite - Understanding NULL Values

In SQLite, NULL represents the absence of a value. It’s a state, not a value itself. A column containing NULL is empty, signifying that no data has been entered for that field.

It is crucial to understand that NULL is distinct from zero (0) or a field containing an empty string (''). Treating them as the same is a common source of bugs in applications.

The Challenge of Three-Valued Logic

Comparisons involving NULL introduce a concept called three-valued logic (TRUE, FALSE, and UNKNOWN). Any direct comparison to NULL using standard operators like =, !=, or > results in UNKNOWN, not TRUE or FALSE. For example, NULL = NULL is UNKNOWN.

Because of this, you must use special operators IS NULL and IS NOT NULL to check for the presence of a missing value.

Syntax for Defining Nullable Columns

When creating a table, you can specify whether a column can store NULL values. If you don’t specify NOT NULL, the column can hold NULLs by default.

CREATE TABLE employees (
   id INTEGER PRIMARY KEY, -- Implicitly NOT NULL
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL,
   department TEXT, -- Can be NULL
   salary REAL -- Can be NULL
);

Here, the department and salary columns are nullable, meaning they can be left empty when a new record is inserted.

Practical Examples

Let’s consider an employees table with the following records:

id | first_name | last_name | age | department | salary
---|------------|-----------|-----|------------|--------
1  | 'John'     | 'Doe'     | 32  | 'Sales'    | 50000.0
2  | 'Jane'     | 'Smith'   | 25  | 'HR'       | 45000.0
3  | 'Peter'    | 'Jones'   | 23  | 'Sales'    | 48000.0
4  | 'Mary'     | 'Williams'| 35  | 'IT'       | 75000.0
5  | 'Chris'    | 'Green'   | 28  | NULL       | NULL

The last record for ‘Chris Green’ has NULL for department and salary, perhaps because they are a new hire whose details are not yet finalized.

To find all employees with a known salary, use IS NOT NULL:

SELECT id, first_name, last_name, salary
FROM employees
WHERE salary IS NOT NULL;

This query returns the first four records, excluding Chris.

id | first_name | last_name | salary
---|------------|-----------|--------
1  | 'John'     | 'Doe'     | 50000.0
2  | 'Jane'     | 'Smith'   | 45000.0
3  | 'Peter'    | 'Jones'   | 48000.0
4  | 'Mary'     | 'Williams'| 75000.0

To find all employees with a missing department, use IS NULL:

SELECT id, first_name, last_name, department
FROM employees
WHERE department IS NULL;

This query returns only the record for Chris Green.

id | first_name | last_name | department
---|------------|-----------|------------
5  | 'Chris'    | 'Green'   | NULL

Common Pitfalls and Best Practices

1. Incorrect Comparisons: Never use column = NULL or column != NULL. It will not work as expected. Always use IS NULL and IS NOT NULL.

2. Aggregate Functions: Functions like COUNT(column_name), SUM(), and AVG() ignore NULL values. COUNT(*) counts all rows, while COUNT(salary) would only count rows where salary is not NULL. Be mindful of this difference.

3. Providing Default Values: When querying data, NULL can be inconvenient. Use the COALESCE() function to provide a default value if a column is NULL. It returns the first non-NULL value in its argument list.

-- Display 'Unassigned' if department is NULL
SELECT first_name, COALESCE(department, 'Unassigned') as department
FROM employees;

This powerful function simplifies handling NULL values in your application code.