Comparing NULL able columns

[Andreas Reichel]

Greetings.

please let me ask for some brain storming:

I have a table COUNTER_PARTY and a matching staging table IMP_COUNTERPARTY.

Now I want to find any records of COUNTER_PARTY which will need to be updated according to IMP_COUNTERPARTY by comparing all the columns. If one of the columns has a different value, I would need to update the record in COUNTER_PARTY.

So far, so simple -- unless NULLs enter the stage, because always: NULL != NULL.

What would be the most efficient way to check, if two values are equal or both are NULL without writing this out verbosely:

select *

from COUNTER_PARTY a

left join IMP_COUNTERPARTY b

on a.id = b.id

where

  ( a.name != b.name or (a.name is null and b.name is null) )

  or ( a.first_name != b.first_name or (a.first_name is null and b.first_name is null) )

I know, that for Strings I can write it as

   NVL( a.name, '') != NVL( b.name, '')

However, this fails for DECIMALS, e.g.

  NVL(  a.age, 0) != NVL( b.age, 0) wont work when age is defined as DECIMAL(3,0)

Can't we have a simplified Compare() or Equal() function?

 Thank you for any input, best regards

Andreas

[Andreas Reichel]

Sorry for the noise. Chat GPT is smarter than me, I should have tried that first:

In SQL, you can compare two nullable columns efficiently to find out if they hold different values using the `IS DISTINCT FROM` or `IS NOT DISTINCT FROM` operators. These operators are typically supported in databases like PostgreSQL, but may not be available in all database systems. Here's how you can use them:

1. Using `IS DISTINCT FROM`:

This operator returns true if the two expressions are not equal, even if one or both of them are NULL.

```sql

SELECT *

FROM your_table

WHERE column1 IS DISTINCT FROM column2;

```

In this query, it will return rows where `column1` and `column2` have different values, including cases where one or both columns are NULL.

2. Using `IS NOT DISTINCT FROM`:

This operator returns true if the two expressions are equal, including cases where both are NULL.

```sql

SELECT *

FROM your_table

WHERE column1 IS NOT DISTINCT FROM column2;

```

In this query, it will return rows where `column1` and `column2` have the same values, including cases where both columns are NULL.

Choose the operator that best fits your specific requirements for handling NULL values. If you want to consider NULL values as different values, use `IS DISTINCT FROM`. If you want to treat NULL values as equivalent, use `IS NOT DISTINCT FROM`. Keep in mind that the availability of these operators may vary depending on the SQL database system you're using, so check your database's documentation for compatibility.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9d5b77d808f333256e648a894c6d8fc60bd63bf3.camel%40manticore-projects.com.

[Evgenij Ryazanov]

Hello!

SQL actually has three boolean values: TRUE, FALSE, and UNKNOWN (UNKNOWN in the same thing as NULL). Comparison operators can return all these values. NULL is neither equal nor not equal to NULL or any other value, results of all six comparison operators (=, <>, <, >, <=, >=) return UNKNOWN if either operand is null (and in some other cases), they don't return FALSE here.

Distinct predicate is different, it always returns TRUE or FALSE, even when comparison operation between operands cannot determine their equality or not equality. In this predicate NULL is not distinct from other NULL. Equal values are not distinct from each other. Non-equal values are distinct from each other and from NULL. IS NOT DISTINCT FROM is a null-safe equivalent of =, IS DISTINCT FROM is a null-safe equivalent of <>.

Various filtration clauses (WHERE, HAVING, etc.) preserve rows where expression evaluates to TRUE only. CHECK and domain constraints allow rows / values where expression evaluates to TRUE or UNKNOWN.

[Andreas Reichel]

On Mon, 2023-09-04 at 20:41 -0700, Evgenij Ryazanov wrote:

IS DISTINCT FROM is a null-safe equivalent of <>

Thank you much for the explanation.

I was not aware of this clause (after so many years of using SQL on many RDBMS).

It is exactly what I was looking for and now I only need to check out the support on the different RDBMS.

Best regards

Andreas