Menu
Is there a nicer way of comparing if two values are equal if they can be nulls other than the following?
a = b or (a is null and b is null)
508
How to Test for NULL Values? It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.
To handle NULLs correctly, SQL provides two special comparison operators: IS NULL and IS NOT NULL. They return only true or false and are the best practice for incorporating NULL values into your queries. Now the query will return every row, as we expected.
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.
In SQL null is not equal ( = ) to anything—not even to another null . According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null .
You can:
a=b or coalesce(a,b) is null
You could also use nvl, but that is a legacy function and coalesce is quicker, since it stops at the first non-null
118
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
