Avoid the Trap with Explanation
At first my solution was \n\n  sql\n  SELECT id, \n  CASE WHEN p_id is null THEN \'Root\'\n       WHEN id NOT IN (SELECT p_id FROM tree) THEN \'Leaf\'\n       E
- At first my solution was \n\n  ```sql\n  SELECT id, \n  CASE WHEN p_id is null THEN \'Root\'\n       WHEN id NOT IN (SELECT p_id FROM tree) THEN \'Leaf\'\n       ELSE \'Inner\'\n       END \n       AS type\n  FROM tree;\n  ```\n\n  but couldn\'t figure out where went wrong. \n\n  After some research, I realize that the statement `SELECT p_id FROM tree` might contain `NULL`, take the first example, the set of the result returned from this statement is `(NULL,1,2)`, and `id NOT IN (SELECT...)` checks the condition if `id != NULL`. Based on the official doc, *You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL*, all of such comparison would return` false`. Threrfore the statement `     WHEN id NOT IN (SELECT p_id FROM tree) THEN \'Leaf\'` actually compares the `id` with `null` in some cases such as example 1.\n\n- When use `CASE`, use `END`.
['MySQL']