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 [email protected]. > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/216681b6bf9efa0be31bef3588395d00fc01626e.camel%40manticore-projects.com.
