SAP ASE ignores any null values in the column on which an aggregate function is operating for the purposes of the function [except count[*] and count_big[*], which includes them].
If you have set ansinull to on, SAP ASE returns an error message whenever a null value is ignored. See the Reference Manual: Commands.
For example, the count of advances in the titles table is not the same as the count of title names, because of the null values in the advance column:
select count[advance] from titles
------------- 16 [1 row affected]
select count[title] from titles
------------- 18 [1 row affected]
If all the values in a column are null, count returns 0. If no rows meet the conditions specified in the where clause, count returns 0. The other functions all return NULL. Here are examples:
Note: When specifying a column that contains nulls as a grouping column [that is, in the GROUP BY clause] for an aggregate function, nulls in the column are treated as equal for the purposes of grouping. This is the one exception to the rule that nulls are not equal to other nulls.You should not try to prevent NULL values - instead, write your query in a way to overcome its limitations.
2 years ago • 6 min read
Table of contents
The NULL value is a data type that represents an unknown value. It is not equivalent to empty string or zero. Suppose you have an employee table containing columns such as
INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
6, INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
7, INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
8 and an alternate contact number. This table has a few mandatory value columns like INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
6, INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
7, and INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
8. However, an alternate contact number is not required and therefore has an unknown value. Therefore a NULL value in this table represents missing or inadequate information. Here are other meanings NULL can have:- Value Unknown
- Value not available
- Attribute not applicable
In this post we will consider how NULL is used in creating tables, querying, string operations, and functions. Screenshots in this post come from the Arctype SQL Client.
Allowing NULL in CREATE TABLE
To a table structure, we need to define whether the respective column allows NULL or not. For example, look at the following customer's table. The columns such as
Select * FROM Customers WHERE Emailaddress=NULL
2, Select * FROM Customers WHERE Emailaddress=NULL
3, Select * FROM Customers WHERE Emailaddress=NULL
4 do not allow NULL values, whereas the Select * FROM Customers WHERE Emailaddress=NULL
5, Select * FROM Customers WHERE Emailaddress=NULL
6, and Select * FROM Customers WHERE Emailaddress=NULL
7 columns can store NULL values.CREATE TABLE Customers[
CustomerID SERIAL PRIMARY KEY,
FirstName varchar[50] NOT NULL,
MiddleName varchar[50] NULL,
LastName varchar[50] NOT NULL,
Suffix varchar[10] NULL,
CompanyName varchar[128] NULL,
SalesPerson varchar[256] NULL,
EmailAddress varchar[50] NULL
]
Let’s insert a few records into this table using the following script.
INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
Using NULL in the WHERE Clause
Now, suppose you want to fetch records for those customers who do not have an email address. The following query works fine, but it will not give us a row:
Select * FROM Customers WHERE Emailaddress=NULL
In the above select statement expression defines “Where the email address equals an UNKNOWN value”. In the SQL standard, we cannot compare a value to NULL. Instead, you refer to the value as IS NULL for this purpose. Note: There is a space between IS and NULL. If you remove space, it becomes a function ISNULL[].
Integer, Decimal, and String Operations with NULL
Similarly, suppose you declared a variable but did not initialize its value. If you try to perform an arithmetic operation, it also returns NULL because SQL cannot determine the correct value for the variable, and it considers an UNKNOWN value.
SELECT 10 * NULL
SELECT 10.0 * NULL
NULL also plays an important role in string concatenation. Suppose you required the customer's full name in a single column, and you concatenate them using the pipe sign[||] .
SELECT Suffix, FirstName, MiddleName, LastName, Suffix,
[Suffix || ' ' || FirstName || ' ' || MiddleName || LastName ] AS CustomerFullName FROM Customers
Look at the result set - the query returns NULL in the concatenated string if any part of the string has NULL. For example, the person in Row 1 does not have a middle name. Its concatenated string is NULL as well, because SQL cannot validate the string value contains NULL.
There are many SQL functions available to overcome these NULL value issues in string concatenations. We’ll look at them later in this article.
The NULL value in SQL Aggregates
Suppose you use aggregate functions such as SUM, AVG, or MIN, MAX for NULL values. What do you think the expected outcome would be?
SELECT Sum[values] AS sum
,avg[values] as Avg
,Min[Values] as MinValue
,Max[Values] as MaxValue
FROM [VALUES [1], [2], [3],[4], [NULL]] AS a [values];
Look at the above figure: it calculated values for all aggregated functions. SQL ignores the NULLs in aggregate functions except for COUNT[] and GROUP BY[]. You get an error message if we try to use the aggregate function on all NULL values.
SELECT
Sum[values] AS sum
,avg[values] as Avg
,Min[Values] as MinValue
,Max[Values] as MaxValue
FROM [VALUES [NULL], [NULL], [NULL],[NULL], [NULL]] AS a [values];
ORDER BY and GROUP BY with NULL
SQL considers the NULL values as the UNKNOWN values. Therefore, if we use ORDER By and GROUP by clause with NULL value columns, it treats them equally and sorts, group them. For example, in our customer table, we have NULLs in the
Select * FROM Customers WHERE Emailaddress=NULL
8 column. If we sort data using this column, it lists the NULL values at the end, as shown below.SELECT Suffix, FirstName, MiddleName, LastName, Suffix,
[Suffix || ' ' || FirstName || ' ' || MiddleName || LastName ]
AS CustomerFullName
FROM Customers
Order BY MiddleName
Before we use GROUP BY, let's insert one more record in the table. It has NULL values in most of the columns, as shown below.
INSERT INTO Customers [FirstName,MiddleName,LastName,Suffix,CompanyName,
SalesPerson,EmailAddress]
values['Sant',NULL,'Joseph',NULL,NULL,NULL,NULL];
Now, use the GROUP BY clause to group records based on their suffix.
INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
0As shown above, SQL treats these NULL values equally and groups them. You get two customer counts for records that do not have any suffix specified in the customers table.
Useful Functions for Working with NULL
We explored how SQL treats NULL values in different operations. In this section, we will explore a few valuable functions to avoid getting undesirable values due to NULL.
Using NULLIF in Postgres and MySQL
The NULLIF[] function compares two input values.
● If both values are equal, it returns NULL.
● In case of mismatch, it returns the first value as an output.
For example, look at the output of the following NULLIF[] functions.
INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
1INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
2INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
3COALESCE function
The COALESCE[] function accepts multiple input values and returns the first non-NULL value. We can specify the various data types in a single COALESCE[] function and return the high precedence data type.
INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
4INSERT INTO Customers
[FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress]
VALUES
['John',NULL,'Peter',NULL,NULL,NULL,NULL],
['Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'],
['Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com']
5Summary
The NULL value type is required in a relational database to represent an unknown or missing value. You need to use the appropriate SQL function to avoid getting undesired output for operations such as data concatenation, comparison, ORDER BY, or GROUP BY. You should not try to prevent NULL values - instead, write your query in a way to overcome its limitations. This way you will learn to love NULL.