NSQL - Rows with null values are not shown

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We have a column in the Database that has NULL values in it. When we fetch that column using a SQL/NSQL query those NULL values are not being returned. Why is this?

Environment

Latest version tested against: 15.9.2

Cause

1. The reason why NULL values would be left out is because of a comparison such as "column != certain_value" condition in the WHERE clause

Example: If we consider the following query, then a condition such as "value2 != 'Expense' " will result in NULL values against value2 column being left out

SELECT
    matrixrowkey,
    fromdate,
    todate,
    value2
FROM
    ppa_matrixvalues
WHERE
    value2 != 'Expense';

2. Whenever a comparison is made against a column in the WHERE clause, the NULL values do not satisfy either the Truth or the False condition
3. This is a standard behaviour across all RDMS products - Postgres, Oracle etc.

Resolution

Adding the following additional line in the WHERE clause will fetch the NULL values also.

OR value2 IS NULL

The Example query will therefore be of the following form:

SELECT
    matrixrowkey,
    fromdate,
    todate,
    value2
FROM
    ppa_matrixvalues
WHERE
        value2 != 'Expense'
    OR
        value2 IS NULL;