Geek Answers Handbook

Using IN / NOT IN in mySQL

For some reason, these two SQL statements are not equivalent. Can someone help to understand why?

SELECT N, IF(P IS NULL, 'Root', IF(N NOT IN (SELECT P FROM BST), 'Leaf', 'Inner'))
FROM BST
ORDER BY N;

SELECT N, IF(P IS NULL, 'Root', IF(N IN (SELECT P FROM BST), 'Inner', 'Leaf'))
FROM BST
ORDER BY N;

PS - This is a binary search tree problem on HackerRank. https://www.hackerrank.com/challenges/binary-search-tree-1

+4
source share
3 answers

jarlh comment above i.e. nothing compares TRUE to NULL except the IFNULL or 'null test. Therefore, if the request:

(SELECT P FROM BST)

returns one NULL, two SQL statements return different results. This can be easily demonstrated using the following two SQL statements:

select if (1 in (select null), 'a', 'b') => 'b'

select if (1 not in (select null), 'b', 'a') => 'a'
+2
source

. NOT IN , NULL. .

, , NOT EXISTS - :

NOT EXISTS (SELECT 1 FROM BST WHERE BST.P = BST.N)

, WHERE P IS NOT NULL .

0

X IN (A,B) X=A OR X=B. OR, TRUE , TRUE. A B NULL, NULL, .

, B , A X. , .

X NOT (A,B) X<>A AND X<>B. AND , TRUE. NULL, NULL, TRUE.

If B is null and X is not A, you will get TRUE OR NULL and get NULL at the end.

0
source

More articles:


All Articles