Wrong predicted SQL Server query outcome

Question

I'm learning for an SQL server exam and I got these questions I answered wrong, but I don't understand why the right answers are right and how you get these.

nr   naam  aantal  chef
1    Anouk  14  2
2    Hans  14  NULL
3    Ali  13  5
4    Kees  12  5
5    Ben  3  2

nr   soort  stad  chef
1    cursus  DenHaag  2
2    cursus  Amsterdam  NULL
3    congres  NewYork  5
4    lezing  Utrecht  5

nr   werknemer  reis  aantal  datum  bedrag
01   1           4  8  17-04-2013  420,56
02   3           3  5  05-04-2013  825,80
03   1           1  5  10-04-2013  140,00
04   null        2  2  10-04-2013  156,75
05   4           4  8  17-04-2013  328,90
06   5           3  5  05-04-2013  560,45

The 2 questions are:

a.

SELECT naam
FROM werknemer
WHERE nr NOT IN (SELECT werknemer
            FROM declaratie);

b.

SELECT naam, COUNT(*)
FROM werknemer w LEFT OUTER JOIN declaratie d ON w.nr = d.werknemer
GROUP BY naam;

My answers are:

a: Hans
b: naam   count(*)
Anouk   2
Hans    0
Ali     1
Kees    1
Ben     1

But the right answers are:

a: none 
b: naam   count(*)
Anouk   2
Hans    1
Ali     1
Kees    1
Ben     1

Can someone explain me what I probably missed?

score 4 · Accepted Answer · edited May 23 '17 at 11:58

4

a) is because performing any kind of NOT IN (1,NULL,3,4,5) returns a NULL results set because SQL cannot say whether any given value is not equal to NULL and thus effectively returns "I don't know". There is an excellent explanation of this on Stack Overflow.

The reason b) is wrong is that you are LEFT JOINing from werknemer. Thus all rows from werknemer are returned - including Hans. COUNT(*) will return a count of rows pertaining to that individual so Hans returns 1. Were you to COUNT(d.werknemer) then Hans would be 0 as all declaratie columns would be NULL for Hans because the JOIN predicate would not be met.

edited May 23 '17 at 11:58

Community

1
1

answered Apr 21 '16 at 12:24

strickt01

3,959
1
17
32

I understand b now. I launched the query from a on SQL server without the NOT and it results all the names except Hans, but you say it(IN (1,NULL,3,4,5)) results in a NULL result set? – Robin van Aalst Apr 21 '16 at 12:44
1

I've clarified my point re a) and provided a link that answers your question very clearly. – strickt01 Apr 21 '16 at 12:55

score 1 · Answer 2 · answered Apr 21 '16 at 12:18

It's very likely that what you miss is that there is no way to compare NULL and INT using '='. This makes your result different, you are considering that comparing 2 (Hans) with NULL from declaratie will return false. You should try running this query to see what do I mean:

if 1=null or 1=1 print 'true' else print 'false'
if 1=null and 1=1 print 'true' else print 'false'

score 1 · Answer 3 · answered Apr 21 '16 at 12:31

Ok, so a is wrong cuz of the null. You cant use in(1,2,3 ,null,5) That will return a nulled resuld and you will recieve none. For b, because you use the left join it means that you get the entire original table with all of the values of the second one. So it means that you get a row for hans but all nulls im the left side because there is no match (but you still count it as a row)

Wrong predicted SQL Server query outcome

3 Answers3