Обсуждение: 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 am using pg 7.3.1 ( I got the same result on pg 7.3) running linux rh 7.3. All IDS and IDS_GRAD are declared as name and do not exists any null's. The encoding is unicode. Pls. help me to resolve the situation. Regards, ivan.
> 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
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@archonet.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
> 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 ? There's an item on it in my Postgresql Notes (subqueries and NULLs), linked to from http://techdocs.postgresql.org You can also read more about it in Bruce's book (also available online from the above URL). Basically, any good relational database book should have a section of the meaning of nulls. - Richard Huxton
----- Original Message ----- > > 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 ? > > There's an item on it in my Postgresql Notes (subqueries and NULLs), > linked to from > > http://techdocs.postgresql.org > > You can also read more about it in Bruce's book (also available online > from the above URL). > > Basically, any good relational database book should have a section of the > meaning of nulls. And why they shouldn't be allowed in a database. ;-) Mike Mascari mascarm@mascari.com > > - Richard Huxton >