MySQL Handling NULL Values

In MySQL, NULL represents missing or unknown data. Proper handling of NULL is essential for accurate queries, updates, and database management. NULL represents missing, unknown, or undefined data and is different from an empty string ('') or zero (0).

Characteristics of NULL Values

• Indeterminate Value: NULL means the value is unknown or not applicable.
• Non-Comparable: NULL cannot be compared using = or != instead this use IS NULL or IS NOT NULL.
• Impact on Operations: Any operation with NULL usually results in NULL.
• Ignored in Aggregates: Aggregate functions typically skip NULL values.

Creating a Table with NULL Values

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NULL,
    salary DECIMAL(10, 2) NULL
);

Creating a Table with the NOT NULL Constraints:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

Inserting NULL Values

We can insert NULL values into the columns that allow them:

INSERT INTO employees (name, salary) VALUES ('Alice', NULL);
INSERT INTO employees (name, salary) VALUES (NULL, 50000);

Output:

Querying NULL Values

To handle NULL values in queries we must use specific SQL functions and operators. The Standard comparison operators like = or != do not work with the NULL.

Checking for NULL Values

To check if a column is NULL use the IS NULL operators.

SELECT * FROM employees WHERE salary IS NULL;

Output:

Checking for NOT NULL Values

To check if a column is NULL use the IS NOT NULL operators.

SELECT * FROM employees WHERE name IS NOT NULL;

Output:

Using COALESCE to Handle NULL Values

The COALESCE function returns the first non-NULL value in the list of the arguments.

Query:

SELECT name, COALESCE(salary, 0) AS salary FROM employees;

Output:

• COALESCE(salary, 0) replaces NULL salaries with 0.
• The query shows each employee’s name and salary, with no NULL values in the result.

Updating NULL Values

When updating records we can set columns to NULL explicitly.

Query:

UPDATE employees SET salary = NULL WHERE salary IS NOT NULL;

Output:

• Assigns NULL to the salary column.
• Updates only rows where salary is not already NULL.

Handling NULL Values in Aggregations

The Functions like COUNT(), SUM(), AVG() etc. handle NULL values in specific ways:

• COUNT(column_name) counts the number of the non-NULL values in the column.
• SUM(column_name) ignores NULL values in the summation.
• AVG(column_name) calculates the average of the non-NULL values.

Example:

SELECT COUNT(salary) AS count_salary, SUM(salary) AS total_salary, AVG(salary) AS average_salary FROM employees;

Output:

• COUNT(salary), SUM(salary), and AVG(salary) ignore NULL salaries.
• The query returns the total number of salaries, their sum, and the average, considering only non-NULL values.

Using Default Values

We can specify default values for columns that are applied when no value is provided during insertion.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2) DEFAULT 0.00
);
INSERT INTO products (name) VALUES ('Laptop');

In this example, if the price is not provided it will default to the 0.00.

NULL Values and Indexes

The NULL values can affect the indexing and query performance. Be mindful of how NULL values impact index creation and optimization.

Creating an Index with NULL Values

CREATE INDEX idx_salary ON employees(salary);

The Indexes will include NULL values which can affect query performance.

Working with NULL Values in MySQL

It involves understanding how missing or undefined data is stored and handled in queries, comparisons, and conditions.

Example 1: Handling NULL in SELECT Queries

To Retrieve employees with known and unknown salaries.

Query:

SELECT name, COALESCE(salary, 0) AS salary_status FROM employees;

Output:

• COALESCE(salary, 'Not Provided') replaces NULL salaries with the text 'Not Provided'.
• The query shows each employee’s name and salary status, ensuring no NULL values appear in the result.

Example 2: Using NULL in Aggregations

To Calculate total and average salary excluding NULL values.

Query:

SELECT SUM(salary) AS total_salary, AVG(salary) AS average_salary FROM employees;

Output:

• SUM(salary) adds up all non-NULL salaries, and AVG(salary) calculates the average of non-NULL salaries.
• The query returns the total and average salary of employees, ignoring any NULL values.