CODEWITHSUNDEEP
sqlpostgresql
Sean McCarthy
Sean McCarthy

Reputation: 5558

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

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:

PostgreSQL record showing 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:

record no longer return; zero records returned

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

Upvotes: 2

Views: 1893

Answers (1)

Sean McCarthy
Sean McCarthy

Reputation: 5558

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: the record I want to be returned

Upvotes: 4

Related Questions