--- In [email protected], 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
