RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent: -- Popular SELECT c.first_name, c.last_name, p.amount FROM customer AS c LEFT JOIN payment AS p ON c.customer_id … Continue reading Think About SQL MERGE in Terms of a RIGHT JOIN
Tag: MERGE
jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more
New dialects: jOOQ 3.20 ships with 2 new experimental dialects: ClickHouse in all editions, including the jOOQ Open Source Edition Databricks in the jOOQ Enterprise Edition ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that is gradually migrated to a more standards compliant alternative, which is why our support is still experimental. … Continue reading jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more
The Many Flavours of the Arcane SQL MERGE Statement
The SQL MERGE statement is a device whose mystery is only exceeded by its power. A simple example shows its full power according to standard SQL. Imagine you have a production table for product prices, and a staging table from which you want to load the latest prices. For once, I'm using the Db2 LuW … Continue reading The Many Flavours of the Arcane SQL MERGE Statement
PostgreSQL Syntax is a Mystery Only Exceeded by its Power
I just ran across this rather powerful PostgreSQL example statement from the manual. It reads Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table: WITH upd AS ( UPDATE employees SET sales_count = sales_count + 1 … Continue reading PostgreSQL Syntax is a Mystery Only Exceeded by its Power
Apache Derby About to Adopt the Awesome SQL:2003 MERGE Statement
Apache Derby is one out of three popular Java embeddable databases (apart from H2 and HSQLDB). It is very SQL and JDBC standards-compliant, but maybe a bit behind on developments of more advanced SQL features. Around 6 years after its first submission, there has now been some action on the Apache Derby DERBY-3155 ticket, recently. Rick Hillegas has attached a first, … Continue reading Apache Derby About to Adopt the Awesome SQL:2003 MERGE Statement
How to simulate MySQL’s INSERT statement extensions
I have previously posted about the SQL MERGE statement, and how powerful it is here: https://blog.jooq.org/arcane-magic-with-the-sql2003-merge-statement/ Unfortunately, not all databases support this statement. Also, very often it is quite a bit of overkill, when what you want to do is to simply INSERT or UPDATE a single record, depending on whether it already exists. MySQL's … Continue reading How to simulate MySQL’s INSERT statement extensions
Arcane magic with the SQL:2003 MERGE statement
Every now and then, we feel awkward about having to distinguish INSERT from UPDATE for any of the following reasons: We have to issue at least two statements We have to think about performance We have to think about race conditions We have to choose between [UPDATE; IF UPDATE_COUNT = 0 THEN INSERT] and [INSERT; … Continue reading Arcane magic with the SQL:2003 MERGE statement