Re: Teach SELECT DISTINCT first!

"Laconic2" <laconic2_at_comcast.net> wrote in message news:<NeGdnUf-jdgqcxbdRVn-vA_at_comcast.com>...
> I do want to talk about SELECT DISTINCT. But I guess some people felt I was
> hijacking someone else's thread. I don't want to do that. So I'll start my
> own thread.
>
> I believe that newcomers to SQL should be taught how to do a SELECT DISTINCT
> before they learn how to do a plain old SELECT. I relaize that this is an
> unusual opinion, but please bear with me.
>
> Consider this query:
>
> SELECT DISTINCT
> E.CITY,
> D.DEPARTMENT_NAME
> FROM
> EMPLOYEES E,
> DEPARTMENTS D
> WHERE
> E.DEPARTMENT_ID = D.DEPARTMENT_ID;
>
>
> The result of this query shows a relation between cities and department
> names. What if we leave off the DISTINCT?
> Well, in that case we get a result table that isn't, in general, in first
> normal form. I think it's worthwhile for students to get used to queries
> that produce a relation in the result, before they branch out into queries
> that don't. So SELECT DISTINCT is actually a more "fundamental" operation
> than "SELECT" is.
>
> Here are the three most common objections to SELECT DISTINCT:
> 1. But it's extra typing!
> 2. But it's extra work for the optimizer!
> 3. But I want those duplicates!
>
> Let's talk about them one at a time:
>
> 1. But it's extra typing!
> Yes, unfortunately it is. But that's the way SQL is defined. You'll learn
> when you can omit the DISTINCT, soon.
>
> 2. But it's extra work for the optimizer!
> So what?
>
> 3. But I want those duplicates!
> Aha! Why do you want to produce a result that is not a relation?

ah, but it only LOOKs like a relation. that's the gotcha.

robert

 Often
> there are legitimate answers to this question. But training the student to
> ask, automatically, whether the desired result is a relation or something
> else, is a good thing.
>
> This ties rather well into the formula already put up in the other thread:
>
> RESULT = PROJECT (RESTRICT (JOIN (A, B)))
>
> When you sepcify DISTINCT, you are really projecting the result onto the
> space CITY, DEPARTMENT_NAME.
Received on Mon Apr 26 2004 - 22:12:14 CEST