I'm confused too, Sasha. I used to write EXISTS(SELECT 1...) myself, but changed to EXISTS(SELECT *...) - most likely after being convinced by someone on this list (Claudio, Ivan or Ann?) that they were equivalent.

Testing a tiny, interactive bit by running

SELECT COUNT(*)
FROM <VariousTablesWithVariousNumberOfRows>
WHERE EXISTS (SELECT [1 | *] FROM TableWithOver1100ColumnsAnd250000Rows)

a few times, doesn't indicate any difference and even

SELECT COUNT(*)
FROM TableWithOver100000Rows
WHERE EXISTS (SELECT [1 | *] FROM TableWithOver1100ColumnsAnd250000Rows)

only needs about 0.1 seconds to return the value (increasing to almost three seconds if I add WHERE to 'join' the tables, but still without any visible difference between * and 1 - in fact, even EXISTS(SELECT FIRST 1 1... seems to be only marginally slower).

Though Seans and Nandos argument that SELECT 1 is more readable is a fair point, and no-one has suggested any drawbacks whatsoever with this option, so I guess I might be changing my habit again...

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sasha Matijasic
Sent: 26. mars 2008 00:30
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Differents results using EXISTS

> >I know he has his answer and this is effectively a closed thread,
> >however I have a related question about it. For these exists clauses,
> >is there a preferred method?
> >
> >1. where exists (select * from some_table where some_where_clause)
>
> Too heavy
>
> >2. where exists (select somefield from some_table where
> >some_where_clause)
>
> Not so heavy, but you're not trying to return a value so why use the
> resources?
>
> >3. where exists (select 1 from some_table where some_where_clause)
>
> Good.
>

Well I guess this thread just brings on more questions, because now I am confused. I always believed those 3 statements are essentially the same because optimizer kicks in and ignores whatever you are trying to select. Am I wrong?

Sasha