SQL is NULL

Question

Hello,

i have a column and this column can be fill with any decription and NULL value.

When i make a filter to showing all records with 'NOT CAPTURED' on the column, why record with NULL value also filtered?

We can see below when i put commend on rca_desc <>'NOT CAPTURED', record with NULL value is exist.

but when i remove the comment, the record with NULL value also filtered.

Answer 1

Hi @Handian Sudianto

When comparing two values x and y, if either x or y is NULL, then some logical comparisons evaluate to an UNKNOWN value rather than true or false. It is called Three-Value Logic.

The query filters (ON, Having, Where) treat UNKNOWN as FALSE. Rows with a filter value of unknown are excluded from the result set.

Refer to this article for some samples: Understanding the Use of NULL in SQL Three-Valued Logic.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Null means the value is unknown, so when compared with something the result is always unknown.

If you want to include rows where rca_desc column is null, you need to use IS NULL. For example:

and (rca_desc <> 'NOT CAPTURED' OR rca_desc IS NULL)

Please see below articles for more information:

https://learn.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql

Thanks.

-TP