Re: UNION ALL and SELECT DISTINCT

David Cressey wrote:
> The nice folks who gave us the SQL language adopted two different
> conventions regarding duplicates in result tables.
>
> In the SELECT operator, they chose to have SELECT mean the same as SELECT
> ALL. If you want SELECT DISTINCT, you have to say so.
>
> Notice that UNION is just the reverse. If you want UNION ALL, you have to
> say so. If you just say UNION, you get only distinct rows in the result.
>
> I think it would have been nicer if they had adopted the same convention in
> both cases.

I might not be thinking clearly, but I think the choices they made make some sense to me. I want the selection and projection to be separate. If I add one attribute to a view, for example, I would not want the number of rows to change just because now what used to be a duplicate row is unique. Or am I missing the point?

> I like the way UNION does it. So I'd like it if SELECT, by
> default, eliminated duplicates. SELECT ALL could be used for what is now
> meant by SELECT.
>
>
> Of course, it's too late to change SQL at this juncture.

SQL is a relatively dead language anyway (which will still be used for many years to come, however) and new languages are being developed, so we might as well make those work for us. It's time for a 2VL and other features in the query language too, so it is fine to think future.

> I'd also like it if the optimizer could figure out when SELECT ALL and
> SELECT DISTINCT are equivalent, and make use of that in picking the optimal
> strategy. If the optimizer has to make a mistake, I'd prefer it to pick a
> strategy that's correct but not optimal, and not pick a fast but incorrect
> strategy.
>
> (sarcasm alert) I would hope most people would agree. But sometimes, I'm
> not so sure they do.

I would guess that most would agree that they would expect accurate and optimize on speed. Cheers! --dawn Received on Wed Apr 05 2006 - 17:20:58 CEST