Search Postgresql Archives

Re: Question about EXISTS

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




That makes sense. Thanks!

On Mon, Jun 25, 2012 at 12:52 PM, David Johnston <polobo@xxxxxxxxx> wrote:
On Jun 24, 2012, at 22:19, Excite Holidays <ehtech1@xxxxxxxxx> 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 TRUE and number1 = 1) in the sub-select and thus all rows are returned from the outer table (because the query inside the exists is no longer linked to the outer query).  As long as at least one record is returned by the standalone query all records will be selected in the outer query.  If no records are returned by the inner query then none will be returned by the outer.  Without the linkage you can never have a result that is a subset of the outer table.


David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux