SELECT wat FROM sql

Re:SELECT wat FROM sql by Pxtl on Monday April 27, 2020 @03:42PM (#22999208)

Do that for two arbitrary variables.

Test that

    t1.f1 = t2.f2

including nulls, for an arbitrary type of f1 and f2.

This is actually a hellscape of gotchas as people try to find a short way to do it and then discover that their short version is treating NULL as equivalent to FALSE, which means it will fail when negated. Or they throw in a COALESCE and then fail because they've now treated a special value as equivalent to NULL.

The correct answer is

    ((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 IS NOT NULL AND t2.f2 IS NOT NULL AND t1.f1 = t2.f2))

[ Parent | on HN ]

Re:SELECT wat FROM sql by da_chicken on Monday April 27, 2020 @05:14PM (#23000106)

First of all, you can shorten that to this:

  ((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 = t2.f2))

That's logically equivalent because TRUE OR UNKNOWN evaluates to TRUE, while FALSE OR UNKNOWN evaluates to UNKNOWN, which is not TRUE and therefore gets excluded.

Second, the complicated one that shows up in MERGE or UPSERT operations is the other way:

  ((t1.f1 IS NULL AND t2.f2 IS NOT NULL) OR (t1.f1 IS NOT NULL AND t2.f2 IS NULL) OR (t1.f1 <> t2.f2))

Third, this is exactly why many RDBMSs support the IS [NOT] DISTINCT FROM syntax defined in ANSI SQL, which replaces the first:

    t1.f1 IS NOT DISTINCT FROM t2.f2

Or this which replaces the second:

    t1.f1 IS DISTINCT FROM t2.f2

MS SQL Server and MySQL don't, but Oracle and PostgreSQL do. It's been in the standard for over 20 years at this point. So it's not the language that sucks; it's your vendor.

[ Parent | on HN ]

Re:SELECT wat FROM sql by Pxtl on Monday April 27, 2020 @10:18PM (#23002640)

And then somebody negates your "is equal" because they want "not equal" statement and tears their hair out.

[ Parent | on HN ]