NSQL - Rows with null values are not shown

book

Article ID: 215802

calendar_today

Updated On:

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?

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.

Environment

Latest version tested against: 15.9.2

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;