> 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