PostgreSQL: "select where" query filtering out records with [null] values

Question

When I run the following simple query:

SELECT gateway, customer_id
FROM gateways
where gateway = '1000056'

Here's the record it returns, showing a [null] customer ID:

When I add another filter, the record is no longer returned, but I think it SHOULD STILL be returned since the customer_id is not equal to 21 :

SELECT gateway, customer_id
FROM gateways
where gateway = '1000056'
    and customer_id != 21 --new filter!

The record is no longer returned:

How can I filter out records whose customer_id = 21, but still return this record where customer_id = [null]?

Sean McCarthy · Accepted Answer · 2021-09-30 18:04:43Z

4

I've found the answer here. Here's a direct quote from that answer:

In SQL, NULL is not equal to anything. Nor is it unequal to anything.

In other words, if I don't tell you my middle name, and you don't tell me your middle name, how can we know if our two middle names are the same name or different names? We can't know.

This often trips people up in SQL, because it's "tri-value logic." An expression can be TRUE, FALSE, or UNKNOWN. Those of us familiar with boolean algebra know that NOT TRUE is FALSE, and NOT FALSE is TRUE.

But the tricky part is that NOT UNKNOWN is still UNKNOWN.

So the solution for you is either always store a non-null string in your column, or else use an expression to account for tri-value logic

This works:

SELECT gateway, customer_id
FROM gateways
where gateway = '1000056'
    and customer_id is distinct from 21

Returning the record I want:

answered Sep 30, 2021 at 18:04

Sean McCarthy

5,7189 gold badges47 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jarlh Over a year ago

To "always store a non-null string" instead of NULL is a bad idea. Null has a purpose!

Collectives™ on Stack Overflow

PostgreSQL: "select where" query filtering out records with [null] values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related