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.
On Mon, 2023-09-04 at 22:25 +0700, Andreas Reichel wrote:
> 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
>
>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/9d5b77d808f333256e648a894c6d8fc60bd63bf3.camel%40manticore-projects.com
> .
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/216681b6bf9efa0be31bef3588395d00fc01626e.camel%40manticore-projects.com.
