Using subquery to create a list for comparison within case statement

Question

I have a table below:

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

I need to write a query to display the following result:

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

First query does not generate the expected output but second query does. The logic seems to be the same. Could you help me figure out the difference?

Query 1:

SELECT id,
CASE WHEN p_id is null THEN 'Root'
     WHEN id not in (select distinct p_id from tree) THEN 'Leaf'
     ELSE 'Inner' 
END AS Type
FROM tree;

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Inner|
| 4  | Inner|
| 5  | Inner|
+----+------+

Query 2:

SELECT id,
CASE WHEN p_id is null THEN 'Root'
     WHEN id in (select distinct p_id from tree) THEN 'Inner'
     ELSE 'Leaf' 
END AS Type
FROM tree;

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

Radu Gheorghiu · Answer 1 · 2019-09-25T05:33:10.250

All you need to change in Query 1 is remove NULL values from the SELECT DISTINCT query.

SELECT id,
CASE WHEN p_id is null THEN 'Root'
     WHEN id not in (select distinct p_id from tree where p_id is NOT NULL) THEN 'Leaf'
     ELSE 'Inner' 
END AS Type
FROM tree;

This is because of how NULL values are treated in your database, based on ANSI_NULLS settings.

From the docs:

"Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

(...)

When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.

(...)

When ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN."

I don't recommend changing your ANSI_NULLS settings, if you don't know what you're doing. Instead, I recommend modifying your query and making sure that each time you use NOT IN, that your sub-query never returns NULL values, by adding a WHERE is NOT NULL condition at the end.

You can find more answers to this problem here on SO, like here(1) and here(2).

score 0 · Answer 2 · answered Sep 25 '19 at 05:28

0

 SELECT
 a2.id,
 CASE
 WHEN a2.p_id is null THEN 'Root'
 WHEN EXISTS
 (
    SELECT DISTINCT a1.p_id FROM tree a1
    WHERE a1.id = a2.id
 )
    THEN 'Inner'
    ELSE 'Leaf'
END
from
tree a2

Note:- I would suggest EXIST instead of IN:

answered Sep 25 '19 at 05:28

THE LIFE-TIME LEARNER

1,476
1
8
18

score 0 · Accepted Answer · answered Sep 25 '19 at 05:54

0

declare @tbl table (id int, p_id int);
insert into @tbl values
(1, null),
(2, 1),
(3, 1),
(4, 2),
(5, 2);

select distinct t1.*,
       case when t2.id is not null and t3.p_id is not null then 'inner'
       when t2.id is null then 'root'
       when t3.p_id is null then 'leaf' end
from @tbl t1
left join @tbl t2 on t1.p_id = t2.id
left join @tbl t3 on t1.id = t3.p_id

The difference is that when using not in against set contaning null it always evalutes to false, so, for example 1 not in (null) would return false (or special value unknown to be precise).

I suggest reading about three-valued logic in SQL :)

answered Sep 25 '19 at 05:54

Michał Turczyn

32,028
14
47
69

Thank you! That makes sense. :) – Trishala213 Sep 25 '19 at 13:19
@Trishala213 if answer helped you, you should mark it as accepyed by checking green check mark on the left. – Michał Turczyn Sep 25 '19 at 14:00

score 0 · Answer 4 · answered Sep 25 '19 at 06:05

Your subquery (select distinct p_id from tree) output the following results (NULL, 1 ,2).

Except for your first row, there is no other place where id is null, that's why you get inner everywhere.

Meaning Where id is not any of these (Null, 1, 2) give me inner.

Using subquery to create a list for comparison within case statement

4 Answers4