Menu
How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in SQL implementations such as Microsoft SQL Server 2008R2 that do not support them?
205
Compares whether two expressions are equal (or not equal). The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator ( = ), which treats NULLs as unknown values.
The opposite of DISTINCT is ALL. Because ALL is the default, it is typically not included.
The DISTINCT clause is used to remove duplicate rows from the result set: SELECT DISTINCT column_list FROM table_name ; Here, column_list is a comma separated list of column or field names of a database table (e.g. name, age, country, etc.)
So the main difference between IS NULL versus IS NOT DISTINCT FROM ? Basically, the IS NULL is used to check if an element is empty. While IS NOT DISTINCT FROM compares 2 elements. Show activity on this post.
The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown.
These predicates work with any comparable type (including rows, arrays and multisets) making it rather complicated to emulate them exactly. However, SQL Server doesn't support most of these types, so we can get pretty far by checking for null arguments/operands:
a IS DISTINCT FROM b can be rewritten as:
((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL)) a IS NOT DISTINCT FROM b can be rewritten as:
(NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL)) Your own answer is incorrect as it fails to consider that FALSE OR NULL evaluates to Unknown. For example, NULL IS DISTINCT FROM NULL should evaluate to False. Similarly, 1 IS NOT DISTINCT FROM NULL should evaluate to False. In both cases, your expressions yield Unknown.
162
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
