NULL Values

A field with a NULL value is a field with no value. It represents "unknown" or "missing data." It is important to understand that a NULL value is different from a zero value or a field that contains spaces.

Database Preparation

Run this block to set up the tables used in the examples below.

CREATE TABLE thesis (
    id INTEGER PRIMARY KEY,
    phd_year INTEGER
);

INSERT INTO thesis (id, phd_year)
VALUES (1, 2020),
       (2, NULL),
       (3, 2021);

CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Discount DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price, Discount)
VALUES (1, 'Apple', 1.00, 0.10),
       (2, 'Banana', 0.50, NULL),
       (3, 'Orange', 1.20, 0.00),
       (4, 'Milk', 3.00, NULL);

SELECT 'Ready' AS Status;

Example:

From a table thesis, to select the id for records where the phd_year is not entered:

SELECT id
FROM thesis
WHERE phd_year IS NULL;

NULL matches the case where the field was left empty. Note that if the field contains, say, a space ' ', then IS NULL will not match!

Combining NULL with Logical Operators (AND, OR, NOT)

Working with NULL can be tricky because it doesn't behave like other values. When you use NULL in a comparison (e.g., column = NULL or column != 5), the result isn't TRUE or FALSE; it's a third state called UNKNOWN.

The WHERE clause only returns rows where the final condition evaluates to TRUE. It discards rows that evaluate to FALSE or UNKNOWN.

Let's use this sample Products table for our examples:

Products Table

Using AND with NULL

The AND operator returns TRUE only if both conditions are TRUE. If one condition is UNKNOWN, the result depends on the other condition.

• TRUE AND UNKNOWN -> UNKNOWN
• FALSE AND UNKNOWN -> FALSE

Operation: Find products with a price less than $2.00 AND a discount greater than 5% (0.05).

Query:

SELECT ProductName FROM Products
WHERE Price < 2.00 AND Discount > 0.05;

Evaluation:

• Apple: Price < 2.00 is TRUE. Discount > 0.05 is TRUE. (TRUE AND TRUE -> TRUE). Row is returned.
• Banana: Price < 2.00 is TRUE. Discount > 0.05 is UNKNOWN (because NULL > 0.05 is unknown). (TRUE AND UNKNOWN -> UNKNOWN). Row is NOT returned.
• Orange: Price < 2.00 is TRUE. Discount > 0.05 is FALSE. (TRUE AND FALSE -> FALSE). Row is NOT returned.

Result:

Using OR with NULL

The OR operator returns TRUE if either condition is TRUE.

• TRUE OR UNKNOWN -> TRUE
• FALSE OR UNKNOWN -> UNKNOWN

Operation: Find products with a price less than $0.60 OR a discount of 10% (0.10).

Query:

SELECT ProductName FROM Products
WHERE Price < 0.60 OR Discount = 0.10;

Evaluation:

• Apple: Price < 0.60 is FALSE. Discount = 0.10 is TRUE. (FALSE OR TRUE -> TRUE). Row is returned.
• Banana: Price < 0.60 is TRUE. Discount = 0.10 is UNKNOWN. (TRUE OR UNKNOWN -> TRUE). Row is returned.
• Orange: Price < 0.60 is FALSE. Discount = 0.10 is FALSE. (FALSE OR FALSE -> FALSE). Row is NOT returned.

Result:

Using NOT with NULL

This is a common pitfall. NOT UNKNOWN results in UNKNOWN. Therefore, using != or NOT on a column with NULL values will not include those NULL rows.

Operation: Find all products that do not have a discount of 0.00.

Incorrect Query:

SELECT ProductName FROM Products WHERE Discount != 0.00;

Evaluation:

• Apple: Discount != 0.00 is TRUE. Row is returned.
• Banana: Discount != 0.00 is UNKNOWN (because NULL != 0.00 is unknown). Row is NOT returned.
• Orange: Discount != 0.00 is FALSE. Row is NOT returned.

Result of Incorrect Query:

This is wrong because it excluded the Banana and Milk, which also don't have a 0.00 discount.

Correct Query: To correctly find all products that are not a specific value, you must also explicitly include rows where the value is NULL.

SELECT ProductName FROM Products
WHERE Discount != 0.00 OR Discount IS NULL;

Result of Correct Query:

Hands-on Exercise

Task: Find the IDs of all theses that have a phd_year entered (i.e., the phd_year is NOT NULL).

Your Query:

-- Write your query here

Expected Results: