Menu
Is there a comparison operator where a.unitnum = b.unitnum would be true if both a.unitnum and b.unitnum are null? Seems that a.unitnum IS b.unitnum is invalid
621
Comparing NULL values Since you can't use a equality operator in the WHERE clause (remember, NULL values can't be equated or compared), the right way to compare NULL values is to use the IS and IS NOT operators.
Use <=> (null-safe equality operator) negated comparison which returns FALSE in case one of the operands is null but TRUE when both are null and both operands have equal non-null values.
SQL has the is [not] null predicate to test if a particular value is null . With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same. Note that you have to use the negated form with not to arrive at similar logic to the equals ( = ) operator.
The value NULL does not equal zero (0), nor does it equal a space (' '). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as '=' or '<>'.
Comparing NULL values Say you have a table with a NULLable column type, and you want to find rows with a NULL value in that column. Since you can't use a equality operator in the WHERE clause (remember, NULL values can't be equated or compared), the right way to compare NULL values is to use the IS and IS NOT operators.
This means that two NULL values aren’t the same, nor are they different. You could make this work by setting ANSI_NULLS OFF, but that breaks a lot of other things and is generally considered ugly.
Comparing NULL values 1 1b. Additionally, Use IS DISTINCT FROM to treat NULL as a known value#N#PostgreSQL has two comparison statements IS... 2 Sorting NULL values#N#When trying to sort a column with the ORDER BY clause on a nullable column, you'll find that NULL... More ...
The long and the short of it is, if you want to do joins on columns that can contain NULL values, be ready for some suprising results. Make sure you always build in some logic that will prevent the crazy results like those seen above.
yes, there is IS DISTINCT FROM and IS NOT DISTINCT FROM
postgres=# \pset null ****
Null display is "****".
postgres=# select null = null;
┌──────────┐
│ ?column? │
╞══════════╡
│ **** │
└──────────┘
(1 row)
postgres=# select null is not distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
postgres=# select 10 = null;
┌──────────┐
│ ?column? │
╞══════════╡
│ **** │
└──────────┘
(1 row)
postgres=# select 10 is distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
postgres=# select 10 is not distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ f │
└──────────┘
(1 row)
postgres=# select 10 is not distinct from 20;
┌──────────┐
│ ?column? │
╞══════════╡
│ f │
└──────────┘
(1 row)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
