Blog

You are currently viewing Mastering Null values in SQL

Mastering Null values in SQL

SQL is such a useful tool for managing and retrieving information stored in relational databases, it’s important to familiarise oneself with the concept of Null values in SQL and how its proper handling can greatly improve database queries. In this post, we’ll talk about the advantages of using Null values in SQL and how to use it into your searches to get the outcomes you want.

What are SQL NULL values?

Data values that do not exist in the database can be indicated using the special marker NULL in SQL. This value can either be the result of an error in the database or simply be an unknown. NULL is not a zero or an empty string; rather, it indicates that the field in question does not contain any information. In a database, a NULL value is used to indicate the absence of data in a column, such as the “MiddleName” field. This occurs when an employee does not have a middle name.

Handling the NULL values in SQL

Understanding the behaviour of SQLL NULL values in various contexts is crucial for working with these values. The following are some considerations to keep in mind when dealing with NULL values:

  • While performing comparisons or operations that involve NULL values, the result that we get is typically NULL.
  • To check for the NULL values, we use the IS NULL condition in a WHERE clause, whereas to check for the NON-NULL values, we use the IS NOT NULL conditions in a WHERE clause.
  • Comparison with NULL can be done using standard comparison operators like “=”, “<” or ”>”, which results in the result being neither true nor false, and is unknown. For checking the NULL values, we must use the conditions ‘IS NULL’ or ‘IS NOT NULL, in our SQL queries.
  • NULL behaves as an unknown value in mathematical operations and any operations that typically involve the NULL values result in NULL. An example of this would be adding a NULL value to a NULL value which results in a NULL value.
  • SQL aggregates functions such as SUM, AVG, COUNT, etc., ignore the NULL values while performing calculations and it can be advantageous when we have to calculate the statistics for a column containing NULL values.
  • SQL provides different functions while working with the NULL values and two of the common functions are, “COALESCE” which returns the first non-NULL value from a list of expressions and is useful for replacing NULL values with a default or alternative value. The “NULLIF” function returns NULL if two expressions are equal and returns the first expressions, this function is handy when we replace a specific value with NULL.
  • The NULL value indexing is done differently than the non-NULL values and this affects the performance of some queries, especially while dealing with huge datasets.

Examples of using SQL NULL values

Here are some of the practical examples of using the SQL NULL values in different scenarios:

Example 1. Inserting NULL values:

This example shows the insertion of a new employee record with no specified department, thus NULL is used for representing this missing information.

INSERT INTO employees (employee_id, employee_name, hire_date, department_id)
VALUES (1, 'John Doe', '2023-01-15', NULL);

Example 2. Querying row with NULL values:

This query will help us retrieve all employee records where department_id is not specified.

SELECT * FROM employees WHERE department_id IS NULL;

Example 3. Replacement of NULL values with Default Values:

The ‘COALESCE’ function can be used here for replacing the NULL hire date with “NOT SPECIFIED in the result set.

SELECT employee_name, COALESCE(hire_date, 'Not specified') AS hire_date FROM employees;

Example 4. Calculating the average by ignoring the NULL Values:

The ‘AVG’ function can be used for computing the average salary of all employees, excluding the ones with NULL salaries.

SELECT AVG(salary) FROM employees;

Example 5. Joining Tables with NULL values:

We can join the tables to retrieve all orders even when there is no corresponding customer. In the query below, LEFT JOIN is used for retrieving all orders.

SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Applications of SQL NULL values

The SQL NULL values serve various practical purposes in database management and are used in a range of applications for handling missing or unknown data. Some of the common applications of SQL NULL values are:

  1. Missing data: In the real world, all the information is not available for every record and NULL values represent missing data which allows us to differentiate missing and known values.
  2. Optional data fields: The NULL values allow us to create tables with certain optional fields. For example, if we have a “PhoneNumber” column that is not required necessarily and the customer doesn’t provide their phone number then we can store NULL in that field.
  3. Default values: In cases where data is not present, we can set default values to NULL, which indicates that no specific values are assigned to that.
  4. NULL values support flexible schema design and we can add new columns to a table without the requirement of existing rows to have values for those columns and is particularly useful when we have to extend existing database schemas.
  5. Temporal data: NULL values are used to represent periods in certain temporal databases where data is not applicable. For example, if we have a database of historical weather data and a particular weather parameter was not recorded for a specific date, then we can use NULL to represent the absence of data.

Advantages of using SQL NULL values

The SQL NULL values provide a lot of advantages in database management and data modelling that are essential in SQL query writing:

  1. Flexibility in data modelling: NULL values allow us to create flexible database schemas through which we can design tables with optional columns which makes it easier for us to accommodate a wide range of data scenarios without needing value for every field.
  2. Space efficiency: NULL values occupy a little storage space where efficiency is very important in databases with large databases and it minimizes our storage requirements.
  3. Differentiating missing values: The NULL values help us distinguish between missing data and actual data and this differentiation is crucial for accurate data analysis and reporting which helps us identify whether data is genuinely absent.
  4. Simplifying data entry: When users or applications interact with a database, they omit optional fields without providing a placeholder value and this simplifies the data entry process and reduces the need for default or placeholder values.  
  5. Flexible schema evolution: With the change in requirements of data over time, we can now extend the existing database schemas by adding new columns without the need to update existing records. The new columns have NULL values for existing records until they are populated with data.
  6. Reduced data integrity issues: NULL values can prevent data integrity issues which might occur if we use placeholder values such as 0 or an empty string for representation of missing data. Through NULL, we can distinguish between a legitimate value of 0 or an empty string and an actual missing value.
  7. Support for working with complex queries: SQL NULL values enable the development of complex queries and we can use them in conditional expressions, aggregations, joins, and other query constructs for creating sophisticated data analysis and retrieval routines.

Conclusion

It is essential to understand how we can work with SQL NULL values for writing robust SQL queries and maintaining accurate databases. This blog helps us understand SQL NULL values in a detailed way and helps us design database schemas by making us understand the behaviour of NULL values and using the built-in functions of SQL to handle them effectively.

If you like the article and would like to support me, make sure to: