What is the difference between these two SQL function?-CodePudding

I am new to SQL and I've been recently solving a SQL problem on hackerrank.com(LINK to the problem) and I found out that two exactly(as far as I know) same code give different outputs.

The input table is this. The table name is 'bst'.

Column	Type
N	Integer
P	Integer

N	P
1	2
3	2
5	6
7	6
2	4
6	4
4	15
8	9
10	9
12	13
14	13
9	11
13	11
11	15
15	Null

It is a simple table with two columns named 'N' and 'P'. I had to define whether each 'N' is a 'root' or 'leaf' or 'inner' node. So I made this query as an answer to this problem. I wrote it in MySQL.

SELECT N,
CASE 
    WHEN P IS NOT Null AND N NOT IN (SELECT P FROM bst) THEN 'Leaf'
    WHEN P IS Null AND N IN (SELECT P FROM bst) THEN 'Root'
    ELSE 'Inner'
END
FROM bst
ORDER BY N;

This did not give me any 'leaf' node and it only printed out 'inner' or 'root'. Following is the result.


1	Inner
2	Inner
3	Inner
4	Inner
5	Inner
6	Inner
7	Inner
8	Inner
9	Inner
10	Inner
11	Inner
12	Inner
13	Inner
14	Inner
15	Root

But there was nothing wrong with my logic so as I was troubleshooting this and that, I found out that if I just change '(SELECT P FROM bst)' part into actual row values like '(2,2,6,6,4,4,15,9,9,13,13,11,11,15)' only for conditions regarding 'leaf', it gives me the right answer. The code is as follows. (It is identical to the code above but only the call for second WHEN clause is different.)

SELECT N,
CASE 
    WHEN P IS NOT Null AND N NOT IN (2,2,6,6,4,4,15,9,9,13,13,11,11,15) THEN 'Leaf'
    WHEN P IS Null AND N IN (SELECT P FROM bst) THEN 'Root'
    ELSE 'Inner'
END
FROM bst
ORDER BY N;

This gave me the following result and this is the right answer for the problem.


1	Leaf
2	Inner
3	Leaf
4	Inner
5	Leaf
6	Inner
7	Leaf
8	Leaf
9	Inner
10	Leaf
11	Inner
12	Leaf
13	Inner
14	Leaf
15	Root

I couldn't find the difference between 'SELECT P FROM bst' and just the list of the values from column P. Is it something to do with setting alias? If that was the case, then why is it okay to use it in the condition for 'root'? Please help me out.

CodePudding user response：

WHEN P IS NOT Null AND NOT N IN (SELECT P FROM bst) THEN 'Leaf'

SELECT N,
CASE 
    WHEN P IS NOT Null AND NOT N IN (SELECT P FROM bst) THEN 'Leaf'
    WHEN P IS Null AND N IN (SELECT P FROM bst) THEN 'Root'
    ELSE 'Inner'
END
FROM bst
ORDER BY N;

CodePudding user response：

I solved it myself.

The problem with 'SELECT P FROM bst' clause was that it has a null value in its output(15th row). So when there is a null value in the condition, it automatically gave out 'false' and did not compare them to actual non-null values. So, the correct code using nested sentence should be:

SELECT N,
CASE 
    WHEN P IS NOT Null AND N NOT IN (SELECT P FROM bst WHERE P IS NOT NULL) THEN 'Leaf'
    WHEN P IS Null AND N IN (SELECT P FROM bst) THEN 'Root'
    ELSE 'Inner'
END
FROM bst
ORDER BY N;

I added 'WHERE P IS NOT NULL' in the sentence for finding 'Leaf'. This code gives out the right answer.