6 min read – Mar 9, 2023
A NULL value in SQL server is a value that represents the absence of any data. It is not the same as a zero or an empty string. A NULL value means that the value is unknown, undefined, or not applicable. It is used to indicate that a particular field in a database does not contain any data, or the data is missing.
Understanding NULL values is crucial for working with databases, and it is essential to know how to handle them correctly. In this article, we will discuss what NULL values are and how to use them in SQL server. We will also explore some examples of NULL values in the Northwind database.
What is a NULL value?
A NULL value in SQL server 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, zero, or space. It is a unique value that cannot be compared with any other value, including other NULL values.
In SQL server, NULL values can be used in any data type, including numbers, dates, and text. When a column is defined with a NULL value, it means that the column can contain a NULL value in addition to other valid values. For example, if a column is defined as VARCHAR(50) NULL, it means that the column can contain a string of up to 50 characters or a NULL value.
Filter and Query for NULL values in SQL Server
To filter and query for NULL values in SQL Server, you can use the IS NULL and IS NOT NULL operators. The IS NULL operator is used to find rows where a column value is NULL, while the IS NOT NULL operator is used to find rows where a column value is not NULL.
Let’s take some examples of NULL values in the Northwind database, a sample database that is often used to demonstrate SQL server features. Let’s explore some examples of NULL values in this database.
Orders table: The Orders table contains information about customer orders, including order ID, customer ID, employee ID, order date, and required date. In some cases, the required date may not be known or specified. In this case, the required date column will contain a NULL value.
SELECT * FROM Orders WHERE RequiredDate IS NULL;
SELECT * FROM Orders WHERE RequiredDate IS NOT NULL;
This query returns all orders where the required date is not specified or all orders except those for which the required date is not specified, meaning all orders for which RequiredDate is specified, or has a values other than NULL.
Orders and Order Details tables: The Orders and Order Details tables are related tables that contain information about customer orders and the products included in each order. The Order Details table includes columns for the product ID, unit price, quantity, and discount. In some cases, the discount may not be known or specified. In this case, the discount column will contain a NULL value.
To retrieve all orders that contain products with a NULL discount, you can use the following query:
SELECT o.OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
WHERE od.Discount IS NULL;
This query joins the Orders and Order Details tables and retrieves all orders that contain products with a NULL discount.
Setting NULL as Default Values
When creating tables in SQL Server, it’s important to set default values for columns where NULL values are not allowed. This ensures that the column always contains a value and eliminates the need for NULL handling in most cases.
For example, suppose you have a table called “Customers” with a column called “City” that cannot contain NULL values. You can set a default value for the “City” column as follows:
CREATE TABLE Customers2
(
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
City VARCHAR(50) NOT NULL DEFAULT 'Unknown'
);
In this example, the “City” column is defined as NOT NULL, and a default value of “Unknown” is set for the column. If a new record is inserted into the table without specifying a value for the “City” column, the default value of “Unknown” is used.
Here is the result of the CREATE TABLE statement above in the Object Explorer in SQL Server
Handling NULL Values in Functions
When working with SQL Server functions, it’s important to handle NULL values correctly. Some functions may return NULL if one or more input parameters are NULL, while others may ignore NULL values.
For example, the AVG function calculates the average of a set of values. If one or more values are NULL, the AVG function returns NULL. To handle NULL values in the AVG function, you can use the COALESCE function to replace NULL values with a non-NULL value:
SELECT AVG(COALESCE(Quantity, 0)) as Quantity
FROM OrderDetails;
In this example, the COALESCE function replaces NULL values in the “Quantity” column with a value of 0 before calculating the average. You can also use the function ISNULL() to get the same result.
CASE Statements to Handle NULL Values
CASE statements are a powerful tool in SQL Server that can be used to handle NULL values.
Suppose you want to display a list of orders along with the customer name and the name of the employee who took the order. However, some orders do not have an associated employee, resulting in NULL values in the “EmployeeID” column of the “Orders” table.
To handle NULL values in this case, you can use a LEFT JOIN between the “Orders” and “Employees” tables, and then use a CASE statement to handle the NULL values. Here’s an example query:
SELECT o.OrderID, c.CompanyName,
CASE WHEN e.EmployeeID IS NULL THEN 'No employee assigned'
ELSE e.FirstName + ' ' + e.LastName END AS EmployeeName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
LEFT JOIN Employees e ON o.EmployeeID = e.EmployeeID
In this query, the LEFT JOIN between the “Orders” and “Employees” tables will return NULL values for the “EmployeeID” column if there is no associated employee. The CASE statement then checks if the “EmployeeID” column is NULL. If it is, the CASE statement returns the string “No employee assigned”. Otherwise, it concatenates the “FirstName” and “LastName” columns of the “Employees” table to form the name of the employee who took the order.
By using a CASE statement to handle NULL values, you can ensure that your query returns accurate and meaningful results, even in cases where data may be missing or incomplete.
The NULLIF function
The NULLIF function is used to compare two expressions and return NULL if they are equal, or the first expression if they are not equal. This function can be useful for handling cases where a value should be NULL under certain conditions.
Suppose you have a table called “Products” that contains information about products sold by your company, including the unit price of each item. However, some products may have a unit price of zero or negative value, which can cause issues when performing calculations.
To handle these invalid unit prices, you can use the NULLIF function in combination with the IS NULL function to replace them with NULL values. Here’s an example query:
SELECT ProductID, ProductName,
NULLIF(UnitPrice, 0) AS ValidUnitPrice,
CASE WHEN UnitPrice IS NULL THEN 'No price available'
ELSE 'Price available' END AS PriceStatus
FROM Products
In this query, the NULLIF function is used to replace any zero values in the “UnitPrice” column with NULL. This ensures that any calculations performed with the unit price will return accurate results.
The CASE statement then checks if the “UnitPrice” column is NULL. If it is, the CASE statement returns the string “No price available”. Otherwise, it returns the string “Price available”. This can be useful for identifying which products have a valid unit price and which do not.
By using the NULLIF function in this way, you can ensure that your queries are robust and accurate, even when dealing with invalid or missing data.
Conclusion
In conclusion, NULL values can be a challenge to work with in SQL, but there are various functions and techniques that can be used to handle them effectively. Some of the functions that can be used include IS NULL, IS NOT NULL, COALESCE, and NULLIF. Additionally, using CASE statements can be a powerful way to handle NULL values and customize the behavior of your queries.
In the Northwind database examples provided, we saw how NULL values can be handled in various scenarios, such as displaying a list of employees and their territories, identifying the discontinued status of products, and listing orders along with the name of the employee who took them.
It’s important to keep in mind that NULL values can have different meanings in different contexts, and it’s crucial to understand the underlying data and business logic to handle them appropriately. By using the functions and techniques discussed in this conversation, you can ensure that your SQL queries are robust and accurate, even when dealing with NULL values.
Leave a Reply