From: | pginfo <pginfo(at)t1(dot)unisoftbg(dot)com> |
---|---|
To: | dev(at)archonet(dot)com |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Wrong query execution. |
Date: | 2003-01-15 10:23:39 |
Message-ID: | 3E2536AB.E61F9B6A@t1.unisoftbg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Richard,
It is the case.
I have found records with NULL's !
Exist any docs where I can read more about interpreting of NOT IN ?
regards,
ivan.
dev(at)archonet(dot)com wrote:
> > Hi,
> >
> > It is possible that I am wrong, but I can nof find my mistake.
> >
> > I have this 2 querie:
> >
> > delete from a_grad where ids NOT in ( select KL.IDS_GRAD from
> > a_klienti kl ) ;
> >
> > It returns 0 rows are deleted !
> >
> > And the second one:
> >
> > delete from a_grad where ids IN (select G.IDS FROM A_GRAD G WHERE NOT
> > EXISTS ( select * from a_klienti kl where KL.IDS_GRAD = G.IDS) ) ;
> >
> > It returns 356 rows are deleted !
> >
> > I expected that the first will delete also 356 rows.
>
> I'm guessing this is the NULL issue hitting you. What does
>
> SELECT ids_grad FROM a_klienti WHERE ids_grad IS NULL
>
> show you?
>
> For those interested, an example of the NULL vs IN issue can be seen in
> the sample SQL below - just uncomment the 4th insert to tb.
>
> DROP TABLE ta;
> CREATE TABLE ta (id_a int4, a text);
> DROP TABLE tb;
> CREATE TABLE tb (id_b int4, id_a_ref int4);
>
> INSERT INTO ta VALUES (1,'aaa');
> INSERT INTO ta VALUES (2,'bbb');
> INSERT INTO ta VALUES (3,'ccc');
>
> INSERT INTO tb VALUES (1,1);
> INSERT INTO tb VALUES (2,3);
> INSERT INTO tb VALUES (3,1);
> -- INSERT INTO tb VALUES (4,Null);
>
> SELECT count(id_a) AS num_to_delete FROM ta WHERE id_a NOT IN (SELECT
> id_a_ref FROM tb);
> DELETE FROM ta WHERE id_a NOT IN (SELECT id_a_ref FROM tb);
>
> HTH
>
> - Richard Huxton
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Beutin | 2003-01-15 11:12:46 | Re: postgres on a PDA |
Previous Message | dev | 2003-01-15 10:02:57 | Re: Wrong query execution. |