SQL Reference

NULL

NULL in SQL represents missing or unknown data. It's not equivalent to zero or a blank space; it is a distinct concept indicating the absence of any value. Handling NULL values requires special consideration in SQL as operations involving NULL often return NULL themselves. Note that because NULL represents an unknown, comparisons involving NULL yield unknown, not true or false.

Example

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    Address VARCHAR(60) NULL
);

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

SELECT * FROM Employees;

Output

+------------+-----------+----------+---------+
| EmployeeID | FirstName | LastName | Address |
+------------+-----------+----------+---------+
|          1 | John      | Doe      |  NULL   |
+------------+-----------+----------+---------+

Explanation

This example demonstrates the usage of NULL in MySQL. A new table “Employees” is created with a nullable “Address” column. An employee is added without an address, so when selecting all records from the table, the address for this employee is shown as NULL, signifying that no value has been assigned.

Example

SELECT COUNT(*) FROM Orders WHERE CustomerID IS NULL;

Output

count
-----
    5

Explanation

The provided SQL statement is used to count the number of orders where the CustomerID is NULL. The output shows that there are 5 orders with no associated CustomerID.

Example

DECLARE @TestVariable INT
SELECT @TestVariable AS 'Null Output'

Output

| Null Output |
|-------------|
| NULL        |

Explanation

In the given example, an integer variable, @TestVariable, is declared without being assigned a value. When the SELECT statement is executed, the result is NULL, as no value was assigned to @TestVariable.

Example

SELECT
    product_name,
    product_price
FROM
    products
WHERE
    product_price IS NULL;

Output

PRODUCT_NAME    PRODUCT_PRICE
--------------  -------------
Product1        NULL
Product2        NULL

Explanation

The code sample retrieves the names and prices of products from the ‘products’ table where the product price is NULL. NULL here indicates the absence of a value. In the result set, ‘Product1’ and ‘Product2’ have NULL prices, meaning these prices are not set or unknown.

Example

CREATE TABLE Employee (
    Emp_id INTEGER PRIMARY KEY,
    Ename TEXT,
    Salary REAL,
    Commission_PCT REAL
);

INSERT INTO Employee (Emp_id, Ename, Salary)
VALUES (101, 'John', 5000);

Output

sqlite> .headers on
sqlite> SELECT * FROM Employee;
Emp_id|Ename|Salary|Commission_PCT
101|John|5000|

Explanation

In this example, a table Employee is created with four columns: Emp_id, Ename, Salary, and Commission_PCT. While inserting data into this table, the Commission_PCT value was not provided, resulting in NULL value for that field in the resultant output record.