--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> > Out of interest, does SELECT 1 work identically and if so whether it
> > performs any quicker (if this were a very large table) than SELECT *
> > in the sub-select?
> >
> > In other words, I'm unclear how the rows from the sub-select are
> > 'materialised' prior to joining to TableA and whether * is needed
> > return TB.LINE so as to test for TA.LINE = TB.LINE or not (if that
> > makes sense!).
> >
> > e.g.
> > UPDATE TableA TA
> > SET TA.USED = 'T'
> > WHERE EXISTS(SELECT 1 FROM TableB TB WHERE TA.LINE = TB.LINE)
>
> I used to use EXISTS (SELECT 1 FROM ...), but then someone wrote somewhere (probably someone with good knowledge of the source code on this list) that it didn't matter and I just started using *. Unfortunately, I don't remember who wrote this, just that it must have been someone I trusted and that it probably was a few years ago. I haven't done any checking myself.
>
> There's no way subselects can be materialized, at least doing
>
> 'SELECT * FROM RDB$DATABASE
> WHERE EXISTS(SELECT * FROM TableWithAFewMillionRows)
>
> give me the answer in about 0.1 seconds (what puzzles me, is that if I use a WHERE clause on an indexed field where most or very few records match, then this seems to be quicker on Firebird 1.5 than if about half of them match - but using 1 or * doesn't seem to make a difference).
>
> Sorry for not being able to shed more light on the issue of * vs 1,
> Set
>

Thanks Set.

> There's no way subselects can be materialized, at least doing ..

My use of the term 'materialized' may have been misunderstood, it was meant to relate to whether/how the sub-select row is retrieved and compared when * vs 1 are used.

I've a copy of Celko's 'SQL for Smarties' and he outlines the theoretical way this happens but I think says it would never happen this way in reality and is implementation-specific. If someone with intimate knowledge of the FB source (and ideally a humble disposition) could show a briefly summary of the actual steps in resolving the SQL above then that would be much appreciated.

Tom