Day 07 – SQL NULL Values
In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is not the same as an empty string or a zero value. A NULL value indicates that the value is unknown, missing, or not applicable.
When creating tables in SQL, you can specify which columns are allowed to contain NULL values by using the NULL or NOT NULL keywords in the column definition. If a column is defined as NULL, it can contain a NULL value. If a column is defined as NOT NULL, it cannot contain a NULL value.
Here are some examples of using NULL values in SQL:
- Inserting a NULL value into a column:
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', NULL, '2022-03-31', 50000);
In this example, the email column is allowed to contain NULL values, so we insert a NULL value for the email address of the employee.
- Filtering NULL values in a WHERE clause:
SELECT * FROM employees
WHERE email IS NULL;
In this example, we use the IS NULL operator to filter out all employees who do not have an email address.
- Handling NULL values in calculations:
SELECT (salary * 0.10) AS bonus FROM employees;
In this example, if an employee’s salary is NULL, the result of the calculation will also be NULL. To handle this, you can use the COALESCE function to replace NULL values with a default value:
SELECT (COALESCE(salary, 0) * 0.10) AS bonus FROM employees;
In this example, if an employee’s salary is NULL, the COALESCE function will replace it with 0, and the calculation will still produce a result.
Working with NULL values is an important aspect of SQL data management. Understanding how to handle NULL values properly can help you avoid errors and ensure that your queries produce accurate results.