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.

## Answers:

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