> 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
