[数据结构与算法]MySQL CASE WHEN NOT IN

在做LeetCode数据库608. 树节点时，使用CASE WHEN NOT IN时，遇到一个not in不生效的问题

表结构

CREATE TABLE `tb_tree` (
  `id` int(11) NOT NULL,
  `p_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

表数据

INSERT INTO `leetcode`.`tb_tree`(`id`, `p_id`) VALUES (1, NULL);
INSERT INTO `leetcode`.`tb_tree`(`id`, `p_id`) VALUES (2, 1);
INSERT INTO `leetcode`.`tb_tree`(`id`, `p_id`) VALUES (3, 1);
INSERT INTO `leetcode`.`tb_tree`(`id`, `p_id`) VALUES (4, 2);
INSERT INTO `leetcode`.`tb_tree`(`id`, `p_id`) VALUES (5, 2);

问题

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序；

SQL

SELECT
	t.id,
CASE
		
		WHEN ISNULL( t.p_id ) THEN
		'根节点' 
		WHEN t.id NOT IN ( SELECT a.p_id FROM tb_tree a ) THEN
		'叶子节点' ELSE '内部节点' 
	END '节点类型'	
FROM
	tb_tree t;

SELECT
	t.id,
CASE
		
		WHEN ISNULL( t.p_id ) THEN
		'根节点' 
		WHEN t.id IN ( SELECT a.p_id FROM tb_tree a ) THEN
		'内部节点' ELSE '叶子节点' 
	END type 
FROM
	tb_tree t;

已解决

SELECT
	t.id,
CASE
		
		WHEN ISNULL( t.p_id ) THEN
		'根节点' 
		WHEN t.id NOT IN ( SELECT a.p_id FROM tb_tree a WHERE p_id IS NOT NULL ) THEN
		'叶子节点' ELSE '内部节点' 
	END 
FROM
	tb_tree t