Some real life problems using standard SQL are:
- UPDATE does not return number of rows updated.
- There is no "upsert"
- There is no "merge"
- List all tables and explain command are not standard.
Do you plan to cover them on your project ?
- UPDATE does not return number of rows updated.
- There is no "upsert"
- There is no "merge"
- List all tables and explain command are not standard.
Do you plan to cover them on your project ?
This ends up with four slightly different syntaxes and the one that matches the standard wasn't even the one that proposed the feature in the first place! It is what it is. Bottom line: "close enough" is as good as it gets in the database arena, so choose your implementation wisely and go all in.
With that out of the way:
• Multiple vendors support RETURNING from both INSERT and UPDATE statements.
• Upserts exist with most vendors, typically with the ON CONFLICT DO variant.
• Most support MERGE now in the latest versions.
• I don't know of any popular database that lacks support for INFORMATION_SCHEMA queries, so listing all tables is in fact both in the standard and supported by all popular implementations.- What is merge? How is it different from join? How is it different from union?
- SQL does define `information_schema`, which can be used to list tables and columns. We had ideas about introspection and listing tables, but this is not on the roadmap yet. A probable outcome here will be improvements to tooling so you will be able to find tables via autocomplete (LSP).
- There are no plans for EXPLAIN
I believe the author is referencing https://www.essentialsql.com/difference-merge-update/ - which you answered in your first point. There's no UPDATE support yet, therefore there's no MERGE support yet.
It gets hairy when you have columns with composite types. E.g. depending on database, records can be JSON objects, protobufs, structs, or other composite types, like tuples.
It is possible to define semantics to merge each of these, but they vary and the merge operation becomes verbose to allow to handle each weirdness of each of these underlying types.
Merging is also sensitive to NULL vs 0 vs a DEFAULT, plus in some databases, NULL is still allowed even when a column is marked NOT NULL.
You'd almost need a sub-language for the merging operation, specifying how to handle each corner case.