Re: relational algrabra division?
Date: Mon, 15 Dec 2014 20:52:08 +0100
Message-ID: <nvitacolonna-368DA6.20520715122014_at_freenews.netfront.net>
In article <20141214171206.bc7b2df3.jklowden_at_speakeasy.net>, "James K. Lowden" <jklowden_at_speakeasy.net> wrote:
> On Fri, 12 Dec 2014 05:20:48 -0800 (PST)
> -CELKO- <jcelko212_at_earthlink.net> wrote:
>
> > FROM Hangar
> > WHERE NOT EXISTS
> > (SELECT *
> > FROM PilotSkills AS PS2
> > WHERE (PS1.pilot = PS2.pilot)
> > AND (PS2.plane = Hangar.plane)));
> ...
> > The use of the NOT EXISTS() predicates is for speed. Most SQL systems
> > will look up a value in an index rather than scan the whole table.
> > The SELECT * clause lets the query optimizer choose the column to use
> > when looking for the index.
>
> I don't want to criticize an otherwise lucid answer, but SELECT * does
> no such thing!
>
> Any columns mentioned in the SELECT clause (projection) part of the
> subquery are pure anachronism, the result of SQL's
> select-from-where syntactical structure. Because they are not *used*
> the query optimizer can ignore them.
>
> In the above example, the columns "pilot" and "plane" are the ones
> in play, being tested against the PS1 and Hanger tables. Likely the
> index supporting the primary key, {pilot, plane}, will be used.
> Whatever choice is made, the "*" is of no significance. The optimizer
> would have been within its rights to treat "SELECT 1" or "SELECT count
> (*)" equivalently, because the mere presence of a single row is all that
> matters.
>
> For that reason, I always recommend "SELECT 1" to emphasize to the
> reader its role in an existence test.
This is OT, but since it has happened to me recently, there is at least one case where "select *" cannot be used in the subquery, and that's when the subquery contains a "group by" clause ("group by... having..." to be precise). This is one more reason to prefer "select 1" over "select *". But wait, why not use "select null" since it's a don't care? After all, "null" is loaded with different meanings already :)
Nicola
- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---