When working with relational databases in SQL, you may encounter NULL values. A NULL value represents a missing or unknown value in a table. In this article, we’ll provide an overview of NULL values in SQL and how to work with them.
What are NULL Values?
In SQL, a NULL value represents a missing or unknown value in a table. NULL values are not the same as zero, an empty string, or a space. They are distinct values that indicate the absence of a value.
For example, consider a table of employees that includes a “salary” column. If an employee’s salary is unknown, the “salary” column for that row would contain a NULL value.
Working with NULL Values
When working with NULL values in SQL, there are several considerations to keep in mind:
NULL Values in Queries
When querying a table that contains NULL values, it’s important to use the IS NULL or IS NOT NULL operators to check for the presence or absence of NULL values. For example, the following query would return all rows where the “salary” column is NULL:
SELECT * FROM employees WHERE salary IS NULL;
Comparing NULL Values
When comparing values that may contain NULL values, it’s important to use the IS NULL or IS NOT NULL operators to check for NULL values. For example, the following query would return all rows where the “salary” column is greater than or equal to 5000:
SELECT * FROM employees WHERE salary >= 5000 OR salary IS NULL;
Aggregating NULL Values
When using aggregate functions such as SUM, COUNT, AVG, or MAX on a column that contains NULL values, the result may be unexpected. For example, the following query would return the total number of employees and the average salary:
SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary FROM employees;
If the “salary” column contains NULL values, the average salary would be calculated as NULL. To avoid this, you can use the COALESCE or ISNULL function to replace NULL values with a default value.
Handling NULL Values in INSERT and UPDATE Statements
When inserting or updating data in a table that contains NULL values, it’s important to explicitly set the value to NULL if it should be empty or unknown. For example, the following query would insert a new row into the employees table with a NULL value for the “salary” column:
INSERT INTO employees (first_name, last_name, salary) VALUES (‘John’, ‘Doe’, NULL);
If you omit the NULL value, the database will assume that you want to insert an empty string or a space.
Handling NULL Values in Joins
When using JOINs to combine data from multiple tables, it’s important to consider how NULL values will be handled. INNER JOINs only return rows where there is a match in both tables, so any rows with NULL values will be excluded. LEFT JOINs return all rows from the left table, and NULL values will be included for any rows in the right table that do not have a match. RIGHT JOINs return all rows from the right table, and NULL values will be included for any rows in the left table that do not have a match.
Using the NULLIF Function
The NULLIF function is a useful tool for handling NULL values in SQL. It returns NULL if two expressions are equal, and the first expression if they are not equal. For example, the following query would return NULL if the “salary” column is equal to 0, and the actual value of the “salary” column if it is not equal to 0:
SELECT NULLIF(salary, 0) AS adjusted_salary FROM employees;
Using the COALESCE Function
The COALESCE function is another useful tool for handling NULL values in SQL. It returns the first non-NULL expression in a list of expressions. For example, the following query would return the “salary” column if it is not NULL, and the string “Unknown” if it is NULL:
SELECT COALESCE(salary, ‘Unknown’) AS salary_info FROM employees;
Conclusion
Handling NULL values is an important part of working with relational databases in SQL. By understanding how to work with NULL values in queries, comparisons, aggregations, joins, and INSERT and UPDATE statements, as well as how to use the NULLIF and COALESCE functions, you can become a more effective SQL developer and analyst. It’s important to remember that NULL values represent a missing or unknown value in a table and should be treated accordingly in your SQL queries and statements.
If you’re looking to enhance your understanding of SQL, LearnTube offers an array of online courses to suit your needs. LearnTube provides a comprehensive learning experience through its dedicated learning app and WhatsApp bot. Whether you’re a beginner or an experienced learner, our platform offers a wide range of courses to cater to your needs. Browse our extensive selection of courses on our website to gain valuable insights.
