0

This query gives me back 90 826 rows:

SELECT DISTINCT ClientID FROM devices;

Now I check how many of these are present in another table having the same column:

SELECT DISTINCT ClientID FROM devices
WHERE ClientID IN
(
    SELECT DISTINCT ClientID FROM patients
);

This gives back 90 736 rows, so 90 rows should not be in this other table. Lets check this out to be sure:

SELECT DISTINCT ClientID FROM devices
WHERE ClientID NOT IN
(
    SELECT DISTINCT ClientID FROM patients
);

But this gives me back an empty set, 0 rows. This shouldn't be right, so I go further, trying this:

SELECT DISTINCT ClientID FROM v_keszulekek
WHERE ClientID NOT IN
(
    SELECT DISTINCT ClientID FROM devices
    WHERE ClientID IN
    (
        SELECT DISTINCT ClientID FROM patients
    )
);

This one gives me back the 90 rows indeed, but the first version should have worked as well in my opinion.

Clearly I am missing something.

MattSom
  • 2,097
  • 5
  • 31
  • 53
  • 5
    Welcome to the strange world of NULL – Strawberry Jun 17 '20 at 08:49
  • 1
    Add a condition **WHERE CLIENTID IS NOT NULL** along with CLIENTID NOT IN condition – Govind Jun 17 '20 at 08:49
  • 1
    SELECT DISTINCT ClientID FROM devices WHERE *ClientID IS NOT NULL AND** ClientID NOT IN ( SELECT DISTINCT ClientID FROM patients **WHERE ClientID IS NOT NULL** ); – Govind Jun 17 '20 at 08:50
  • Show output of `show create table devices` and `show create table patients`. – ysth Jun 17 '20 at 08:51
  • Indeed `SELECT DISTINCT ClientID FROM patients` returned `NULL` as well as one of the values. Is there some comparison with the `NULL` value going on in the background? – MattSom Jun 17 '20 at 09:07
  • @Strawberry Not strange, I would say, but misunderstood. Null is not a value, and according to SQL means "missing data": i.e the data exists, it's just "we couldn't get it". Therefore `NOT IN` can never return true in the presence of a null since the checked value "may be there"; it may return false; but it can return `UNKNOWN` too -- that is a full blown valid return according to the SQL Standard since the beginning. – The Impaler Jun 17 '20 at 11:07
  • @Govind you should make that an answer – ysth Jun 18 '20 at 06:47

1 Answers1

2

NULL is not a data-type and you cannot use any filter or clause IN, NOT IN etc. You have to explicitly exclude it using IS NULL OR IS NOT NULL condition. Here, you have to perform the below to get the intended output.

SELECT DISTINCT ClientID FROM devices WHERE ClientID IS NOT NULL AND ClientID NOT IN ( SELECT DISTINCT ClientID FROM patients WHERE ClientID IS NOT NULL );
Govind
  • 439
  • 3
  • 6