MySQL - "not null" won't work until you remove NULL values
Wrong way ->\n\nSELECT id,\nCASE\n    WHEN p_id is NULL THEN "Root"\n--> WHEN id NOT IN (SELECT pid FROM Tree) THEN "Leaf"\n    ELSE "Inner" \nEND AS type \nFRO
* Wrong way ->\n```\nSELECT id,\nCASE\n    WHEN p_id is NULL THEN "Root"\n--> WHEN id NOT IN (SELECT pid FROM Tree) THEN "Leaf"\n    ELSE "Inner" \nEND AS type \nFROM Tree\n```\nNote that above highlighted query won\'t work as every time when it will traverse the p_id and when the NOT IN operator compare the "id" with "NULL" it will always return NULL output and hence as a result it will not print anything.\n**To avoid above ambiguity you need to remove NULL values from p_id :**\n\n* Correct Way ->\n```\nSELECT id,\nCASE\n    WHEN p_id is NULL THEN "Root"\n    WHEN id NOT IN (SELECT p_id FROM Tree WHERE p_id IS NOT NULL) THEN "Leaf"\n    ELSE "Inner" \nEND AS type \nFROM Tree\n```
['MySQL']