MySQL NULL Values

Example: Basic Concept of NULL

NULL in MySQL represents missing or unknown data. It is different from an empty string or zero.

Example: Defining a Column to Allow NULL

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date DATE,
    salary DECIMAL(10, 2),
    department_id INT,
    manager_id INT,
    termination_date DATE
);

In this example, the termination_date column is defined to allow NULL values, indicating that not all employees may have a termination date recorded.

Example: Querying Rows with NULL Values

SELECT * FROM employees
WHERE termination_date IS NULL;

This SQL query selects all columns from the employees table where the termination_date is NULL, retrieving employees who have not been terminated.

Example: Inserting a Row with NULL Value

INSERT INTO employees (first_name, last_name, birth_date, hire_date, salary, department_id, manager_id)
VALUES ('Alice', 'Smith', '1990-05-15', '2015-07-01', 60000.00, 101, NULL);

In this SQL statement, the manager_id is assigned a NULL value, indicating that Alice Smith does not report to any specific manager.