What advantage is gained by continuing to not provide an UPSERT statement in T-SQL (or any given major SQL dialect)?

Question

This question is asking about a generally applicable engineering principle. It is using T-SQL as a specific example, but the question is about the engineering principles behind major SQL dialects in general. It is also asked in the same spirit as this earlier question:

What advantage was gained by implementing LINQ in a way that does not cache the results?

T-SQL's been around for a little while, and so has SQL Server, which it goes hand-in-hand with. A lot of T-SQL / SQL Server questions on Stack Overflow, for instance, pertain to the version from 2005. It is now 15 years later.

As of December 2020, there is still no simple, native UPSERT statement. Instead what many people do is to simply check for existence first, then insert or update. However this has concurrency issues, unless you also specify locking. At that point, you have to be conscientious of the scope of different locking mechanisms, and your code becomes a bit long-winded.

Upsert is an operation that is constantly needed; however the decision to still not include it - years and years after T-SQL has been released, updated, and expanded upon - must be conscious and deliberate. There has been an abundance of time to add an UPSERT keyword and native operation into T-SQL / SQL Server, so there's something Microsoft is seeing that's making them wary of doing it - some engineering principle that has to be taken into consideration for any major SQL dialect in general.

So what advantage is gained, from an engineering perspective, by not having an UPSERT keyword/operation in a large-scale SQL dialect? What drawback would the inclusion of this bring into the equation? It can be an advantage for languages to remain relatively slim, only adding new features where there is a clear, lasting advantage. But since upsertion is such a commonly required and clearly defined operation, what other advantage is gained by deliberately leaving it out?

---

This is roughly along the lines of what I'm visualizing. It should be able to use essentially the same internal, automatic locking mechanisms that keep two UPDATE or two INSERT statements out of each other's way. SQL implementations already have to prevent UPDATE statements from concurrently affecting the same row (or INSERT statements from trying to insert the same automatic primary key value at the same time), so we should be able to just use either the exact same or nearly the same locking mechanisms here:

Case 1:

UPSERT [TableX] (ColumnA, ColumnB, ... )
VALUES          (@valueA, @valueB, ... );

↓

IF NOT EXISTS(
    SELECT
        1
    FROM
        TableX WITH (
            <roughly the union of internal, automatic locks used with UPDATE and INSERT
            statements>
        )
    WHERE
        (ColumnA = @valueA OR ISNULL(ColumnA, @valueA) IS NULL) AND
        (ColumnB = @valueB OR ISNULL(ColumnB, @valueB) IS NULL) AND
        ...
)
    INSERT TableX (ColumnA, ColumnB, ... )
    VALUES        (@valueA, @valueB, ... );

Case 2:

UPSERT [TableX] (ColumnA, ColumnB, ... )
VALUES          (@valueA, @valueB, ... )
WHERE           <SetOfConditions>;

↓

IF EXISTS(
    SELECT
        1
    FROM
        TableX WITH (
            <roughly the union of internal, automatic locks used with UPDATE and INSERT
            statements>
        )
    WHERE
        <SetOfConditions>
)
    UPDATE TableX
    SET
        ColumnA = @valueA,
        ColumnB = @valueB,
        ...
    WHERE
        <SetOfConditions>
ELSE
    INSERT TableX (ColumnA, ColumnB, ..., <AnythingElseFrom_SetOfConditions> )
    VALUES        (@valueA, @valueB, ..., <AnythingElseFrom_SetOfConditions> );

To me this seems to follow naturally and logically, using roughly the same internal locking mechanisms that are used to implement UPDATE and INSERT statements. The locking might be slightly more involved, because this is two operations back-to-back, but it should be 80-90% just a rehash of what's already in place for UPDATEs and INSERTs. It should primarily just be a unioning of UPDATE- and INSERT-style locks together.

The only slightly messy part would be the explicit WHERE clause. On the one hand, you basically have to deterministically derive values from it, to implicitly pad them on to the VALUES or SELECT clause. This is so that in the case of insertion, the resulting row will match the WHERE clause. Because of this, WHERE conditions like ColumnZ > 0 will not work without randomness and extra complication, but conditions like ColumnZ = 0 or ColumnZ IS NULL will work.

On the other hand, the conditions in the WHERE clause should not be allowed to contradict the VALUES or SELECT clause. Once again, this has to do with insertion. If a new row has to be inserted, and if the clauses contradict each other, which clause is the new row supposed to match? Because of this issue, contradictions will need to invalidate the statement/operation.

So two things are needed to resolve the two issues above: For the first issue to be resolved, WHERE conditions will need to be deterministic (unless you allow randomness and extra complication there). For the second issue, WHERE conditions should not be allowed to contradict anything in the VALUES or SELECT clause.

To me, as long as the WHERE clause is restricted enough to resolve the two issues above, this seems to make perfect sense, be extremely practical and commonly applicable, and have a solid longevity ahead of it. Be it T-SQL or anything else, why would a major, well-funded, full-scale SQL implementation continue to forgo this feature indefinitely? From an engineering perspective, what's the specific issue that makes this a serious problem?

Answer 1

So what advantage is gained, from an engineering perspective, by not having an UPSERT keyword/operation in a large-scale SQL dialect?

TSQL has an upsert keyword, MERGE. It can have the exact meaning you want UPSERT to have, with the caveat of being more explicit. On the flip side, that explicitness is in the form of INSERT and UPDATE clauses.