0

I would like to know how many serial numbers appear in table 2 but not in table 1. When I run:

SELECT count(DISTINCT sn)
FROM table2
WHERE sn NOT IN (table1.sn)

I get 0.

I am certain there exist serial numbers in table2 not in table1, luckily when I run: 

SELECT count(DISTINCT sn)
FROM table2
WHERE sn NOT IN (
SELECT DISTINCT table1.sn
FROM(
table1 INNER JOIN table2 ON(table1.sn=table2.sn)
) 
)

I get a large number of sn's.

This is not logically possible! It means there are serial numbers in table 2 not in the join, but there are no serial numbers in table 2 not in table 1. I tried casting the results from the query & sub query to text and that didn't work.

Shirig
  • 1
  • 2
  • 3
    `WHERE NOT IN (table1.sn)` is invalid SQL. I guess you didn't show us the real query. But the reason for this behaviour are most probably `NULL` values in that column –  Feb 19 '15 at 08:48
  • Please refer to "NOT IN clause and NULL values" http://stackoverflow.com/questions/129077/not-in-clause-and-null-values – Paul Maxwell Feb 19 '15 at 10:28
  • Sorry, I meant WHERE sn NOT IN...I checked and there are no NULLs in the sn column – Shirig Feb 19 '15 at 10:57
  • Then please *edit* your question to fix it. And provide table definitions and your version of Postgres while being at it. – Erwin Brandstetter Feb 19 '15 at 11:00
  • Got it! indeed the problem was NULL's in table 1. Thanks!!! – Shirig Feb 19 '15 at 13:17

0 Answers0