I've written a lot of SQL. Mostly Microsoft's T-SQL variant. I was a DBA in a high pressure environment looking after over 80 business critical server instances.

It's a language that I'm very confident in, and one that I think is very well suited to the job. However there's one major flaw with how it works that annoys me every time I use it:

SQL has two methods for getting data into a table, depending on whether you're adding or changing data.

--Add
    insert into tablename ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )

--Change
    update tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

There are two problems here, the first is that you need to know whether you're adding or changing before you write the SQL. The problem is that you often don't. You can get around this with an additional bit of logic:

--check whether to insert or update
if not exists(
    select * from tablename where idfield = 7 ) 
    insert into tablename ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )
else
    update tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

Hardly ideal, it does two I/O operations where only one might be required for starters, but that isn't my issue with it. The real problem is the insert statement - it depends on the order of fields. For two or three fields this isn't a problem, but for more than that it quickly gets to be a royal pain.

Oracle has had, for a while now, a solution for the first problem that nicely fixes the second:

    upsert tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

This upsert statement does both update and insert in Oracle, but it also has the huge advantage that you can have one statement that lists fields next to the values you're setting for them. Order doesn't matter.

Now in SQL 2008 Microsoft have 'fixed' this (only they haven't) with the merge statement:

merge into tablename 
where  idfield = 7
when matched then
    update
    set field1 = 'new value',
        field2 = 'different value',
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )

The two I/O operations where one was needed problem has been fixed, but the language issue of lots of inserted fields is still there.

Upsert isn't in the SQL:92 standard, and merge is new to the SQL:2003 standard. It's great that Microsoft have caught up with a five year old standard, but I still prefer the Oracle way of doing it.

It's just about the only thing Oracle does better than Microsoft.