# What is the difference between these two SQL function?

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’.

ColumnType
NInteger
PInteger
NP
12
32
56
76
24
64
415
89
109
1213
1413
911
1311
1115
15Null

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.

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

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.

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

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.

Contents

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

### Method 1

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;```

### Method 2

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0