Re: Question about EXISTS

On Jun 24, 2012, at 22:19, Excite Holidays <ehtech1@gmail.com> wrote:

> Hi,
>
> I have been making some test with EXISTS and I found I case that I do not understand too well:
>
> CREATE TABLE testing (
> number_id serial,
> number1 integer,
> number2 integer
> );
> INSERT INTO testing (number1, number2) VALUES (1,1),(1,2),(2,3);
>
> SELECT *
> FROM testing
> WHERE EXISTS (SELECT 1 FROM testing WHERE testing.number_id = number_id AND number1 = 1);
>
> As far I understand the documentation the select query should return row 1 and 2, but it is returning 1, 2 and 3.
>
> Why is this happening ?
>
> PS_ PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (Debian 4.3.2-1.1) 4.3.2, 32-bit
>
> Regards,
> Ruben
>

When you reference the same table in multiple locations you really should give one of them an alias.

In this case the non-table prefix number_id is resolving to the table inside the exists so you basically get (... WHERE
TRUEand number1 = 1) in the sub-select and thus all rows are returned from the outer table (because the query inside
theexists is no longer linked to the outer query).  As long as at least one record is returned by the standalone query
allrecords will be selected in the outer query.  If no records are returned by the inner query then none will be
returnedby the outer.  Without the linkage you can never have a result that is a subset of the outer table. 


David J.