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.

DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Orders;

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    OrderDate DATE,
    CustomerID INTEGER,
    ShippedDate DATE
);

INSERT INTO Orders (OrderID, OrderDate, CustomerID, ShippedDate)
VALUES (101, '2025-09-01', 1, '2025-09-03'),
       (102, '2025-09-02', 2, NULL),
       (103, '2025-09-03', 1, '2025-09-05');

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);

Database Overview & Sample Data

The database is pre-loaded with the Orders and Products tables. Run the queries below to explore the data.

Orders Table

SELECT * FROM Orders;

Products Table

SELECT * FROM Products;

Example:

From the Orders table, to select the OrderID for records where the order has not yet shipped (ShippedDate is NULL):

SELECT OrderID
FROM Orders
WHERE ShippedDate 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 OrderID of all orders that have already been shipped (i.e., ShippedDate IS NOT NULL).

Your Query:

-- Write your query here

Expected Results: