In SQL and relational databases, NULL values are an important part of managing data and information stored in SQL tables. A NULL value is used as a placeholder for missing values or values that are unknown in a database column or cell. Understanding how to handle NULL values is important for database developers who want to avoid errors and retrieve accurate results when querying a database table. This database development tutorial covers the basics of how to work with NULL values in SQL.
Read: Best Online Courses to Learn SQL
What is a NULL Value in SQL?
In relational databases and RDBMS’, NULL values are a special value in SQL that are used to represent an unknown – or in some instances, a missing – value in a column or cell. A lot of newer database developers think that NULL is the same as a zero value integer or an empty string; however, in reality NULL is a value representing the absence of any value. You should note that NULL values are not equal to any other value, including other NULL values.
For database administrators and programmers working with NULL values in SQL, it is important to understand how this unique value is handled in different situations. For instance, when performing mathematical calculations, NULL values always return a NULL value. Because of this, we need to handle NULL values properly in order to avoid errors in calculations and our SQL queries.
How to Create a Table with NULL Values in SQL
To create a table in SQL, you first specify which columns will allow NULL values. By default, every column will allow NULL values unless programmers say otherwise.
The SQL syntax and statement below shows how to create a table containing NULL values:
CREATE TABLE employees ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100), phone_number VARCHAR(20) NULL, hire_date DATE, job_id INT, salary DECIMAL(10,2) NULL, commission_pct DECIMAL(4,2) NULL, manager_id INT NULL, department_id INT );
In the above code example, we created an employees table with several columns. The phone_number, salary, commission_pct, and manager_id columns can contain NULL values. This is achieved using the NULL keyword.
How to Insert NULL Values into a Table in SQL
You can insert NULL values into a table that allows NULL types by using the NULL keyword. Optionally, you can omit the value altogether to achieve the same result.
For instance, if we want to insert a NULL value into the phone_number column of the employees table from our previous example, we would use the following SQL syntax:
INSERT INTO employees (id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1, 'John', 'Doe', 'johndoe@email.com', NULL, '2023-01-01', 1, 5000.00, 0.10, NULL, 1);
Here, In this SQL example, we inserted a new employee into the employees table. The phone_number and manager_id columns both contain NULL values.
How to Query NULL Values in SQL
If we query data in a relational database that contains NULL values, we must first understand how they are handled in different situations.
For instance, if we want to query data that contains NULL values, we can use the IS NULL or IS NOT NULL keywords. Let’s say we want to select every employee from the employees table who do not have a phone number; in this instance, we would would use the following SQL query:
SELECT * FROM employees WHERE phone_number IS NULL;
In the above example, we selected every column from the employees table where the phone_number column contains a NULL value.
Another way to use NULL values in SQL is to retrieve data where a specific record is not NULL. For instance, if we want to select every employee from the employees table who have a phone number, you could use the following SQL query:
SELECT * FROM employees WHERE phone_number IS NOT NULL;
In this SQL example, we selected all values from the employees table where the phone_number column is not NULL.
Read: SQL Data Types
Using SQL NULL Values in Calculations
If you add a NULL value to a numeric value, the result is always NULL. The same goes for any mathematical operation involving a NULL value. Because of this, it is important for database programmers to use the IS NULL keyword to check for NULL values before performing any mathematical operations in SQL or relational databases.
As an example, if we wanted to calculate the average salary of every employee in our employees table, we could use the SQL query below:
SELECT AVG(salary) AS average_salary FROM employees WHERE salary IS NOT NULL;
In this query, we used the AVG function to calculate the average salary of every employee. Additionally, we used the IS NOT NULL keyword to exclude any employees that have no salary.
SQL COALESCE Function
In SQL, the COALESCE function is used to handle NULL values in SQL. The COALESCE function takes multiple arguments and returns the first non-NULL value. This function is used to replace NULL values with a default value, or if you want to prioritize one value over another.
If we wanted to select the salary of an employee and replace any NULL values in the table with a default value of 0, we could use the following SQL query:
SELECT COALESCE(salary, 0) AS salary FROM employees;
Here, we used the COALESCE function to retrieve the salary column of the employees table. If the value representing salary is NULL, the function will return a default value of 0.
How to Use the IFNULL Function
The SQL IFNULL function works similar to the COALESCE function, but it only takes two arguments. The IFNULL function returns the first argument if it is not NULL; otherwise, the second argument is returned.
To retrieve the salary of an employee and replace all NULL values with a default value of 0, the following SQL query can be used:
SELECT IFNULL(salary, 0) AS salary FROM employees;
This example uses the SQL IFNULL function to return the salary column of the employees table. If the salary is NULL, IFNULL returns a default value of 0.
Final Thoughts on SQL NULL Values
In SQL, NULL values are important for managing data. NULL values are placeholders for missing or unknown values in a column. Understanding how to work with NULL values helps database developers avoid errors and create more accurate results with our queries.
Keep in mind that when you create a table in SQL, database programmers can specify which columns can allow NULL values. By default, all columns will allow NULL values unless otherwise specified. If you want to insert data into a table that allows NULL values, you can insert a NULL value using the NULL keyword. You can also simply omit the value as well.
If you query data that contains NULL values, programmers can use the IS NULL or IS NOT NULL SQL keywords to select data containing NULL values or if you want to exclude data that contains NULL values.
The SQL COALESCE and IFNULL functions are great for handling NULL values in SQL. The COALESCE function uses multiple arguments and returns the first non-NULL value, while the IFNULL function only takes two arguments and returns the first argument if it is not NULL. If not, it returns the second argument.
Read: SQL Best Practices
