Re: relational algrabra division?

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.

--jkl Received on Sun Dec 14 2014 - 23:12:06 CET