Efficiency; advanced/future SQL constructs

There are some operations that are so obvious and frequent that I'm amazed that they didn't show up in SQL, and that I haven't read anything about any planned updates to the standards that might incorporate them. Actually, before I ask anything else, let me ask this--is an update in the works? Or is ANSI SQL now frozen for all time? Meanwhile, in the absence of a direct construct for these operations, I sometimes wonder what the most efficient way of implementing them is, whether in terms of running efficiency or implementation efficiency. In case it affects the answer, I work mostly with SQL Server, though sometimes Oracle comes up.

One was mentioned here recently--having a batch of data to add to a table where each new record should be used to update an existing record if there is one, or to insert a record otherwise. Why wouldn't that be a standard operation? Given that it *isn't* a standard operation, which of the following is more efficient:

1. For each record, do a SELECT to find an existing record, and update on success, insert on failure.
2. Load the new data into a temporary table, do an UPDATE of the form

            update T, TEMP set T.x = TEMP.x [, ...] where T.id = TEMP.id
            [or the same thing with an INNER JOIN if supported]

        and then do an INSERT of the form

            insert into T
            select id, x [, ...] from TEMP
            where not exists (select 'x' from T where T.id = TEMP.id)

        (I assume this last is more efficient than

            where TEMP.id not in (select id from T)

        Correct?)

Here's an example of another routine type of operation. I can write a query to get the highest annual salary of any employee in each of my company's divisions:

    select max(salary) from Employee group by divisionID

But how do I *identify* the highest-paid employees?

    select employeeID, name, max(salary) from Employee group by divisionID

obviously doesn't do it, but it would be nice to have something that simple. Even more complex though possible not quite as general would be a query to return all the records from the Sales table connected with the highest-paid employee in each division.

What IS the best way, at this time, to get this information? IS there a single SQL statement that does it, in either ANSI SQL or a specific dialect?

I'm curious what similar standard operation types others can think of that would be nicely handled by a standard mechanism as well. Received on Tue Aug 21 2001 - 20:22:06 CEST