Re: Wrong query execution.

> 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