Post reply

Using table variables in T-SQL

  • September 10, 2025 at 12:00 am

    #4634973

    Comments posted to this topic are about the item Using table variables in T-SQL

  • September 10, 2025 at 7:13 am

    #4649222

    I must admit I've never seen the construct "IS DISTINCT FROM NULL" before, and initially I thought that must be the error, but it's actually legit. My next thought was that it's a complicated way of saying "IS NOT NULL".

    Actually, it's a bit different. While it doesn't matter for IS NULL / IS NOT NULL, it does make a difference when used with non null values.

    WHERE t.id <> 3 results in 2, whereas WHERE t.id IS DISTINCT FROM 3 results in 3.

    I can think of quite a few cases where this could be a better option than ISNULL().

    Just because you're right doesn't mean everybody else is wrong.

  • September 10, 2025 at 11:10 am

    #4649323

    IS DISTINCT FROM or its counterpart IS NOT DISTINCT FROM are added in SQL 2022 and are very handy when comparing columns that may contain NULL

    So instead of a slow

    WHERE ISNULL(a.col1, '') = ISNULL(b.col1, '')

    or a faster/better but clumsy to write (and easily to fail)

    WHERE ((a.col1 IS NULL AND b.col1 IS NULL) OR (a.col1 = b.col1))

    you can now use

    WHERE a.col1 IS NOT DISTINCT FROM b.col1

    I love this term, since it saves a lot of daily typing, the only bad point is that they used DISTINCT instead of something like EQUAL, because you now have to use a negative impression (NOT DISTINCT) to compare for equality, which is a bit counterintuitive.

    God is real, unless declared integer.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply