Thread: Question about EXISTS
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);
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
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.
That makes sense. Thanks!
On Mon, Jun 25, 2012 at 12:52 PM, David Johnston <polobo@yahoo.com> wrote:
When you reference the same table in multiple locations you really should give one of them an alias.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
>
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.