-1

I'm using Syabse IQ 12.7 ver.

I'm trying to get counts from tables with exists or not in statements.

here's the queries ;

SELECT A.CUSTNO 
  FROM TABLE_A A 
 WHERE A.CUSTNO NOT IN (SELECT B.CUSTNO FROM TABLE_B B)

SELECT A.CUSTNO 
  FROM TABLE_A A 
 WHERE NOT EXISTS(SELECT 1 FROM TABLE_B B WHERE A.CUSTNO = B.CUSTNO)

the thing is "not exists" lets say generates 1000 rows but "not in" generates 0 rows.

Any idea about this problem ? is it bug or something ?

Deniz
  • 169
  • 1
  • 6
  • 16
  • 1
    It seems you are comparing different columns in the two queries. In the first query, you are comparing `A.CUSTNO` to `B.CUSTNO`, where as in the second query, you are comparing `A.CUSTNO` to `B.FMUSNO`. Depending on the values in these columns, you cannot expect the output of the queries to be identical. – Dan Oct 03 '13 at 08:55
  • yes you'r right , i forget to change the real column to dummy one. – Deniz Oct 03 '13 at 10:02
  • 1
    http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null should explain why. – Hotel Oct 03 '13 at 14:56
  • thank you Hotel , the problem was just one NULL value from TABLE_B.CUSTNO and also i got some usefull info from the link. – Deniz Oct 04 '13 at 06:39

0 Answers0