Search code examples
sqldatabaseselectin-clauseexasol

Why select IN doesnt work properly when there are nulls

Why this is returning a record in EXASOL DBMS

SELECT a, b FROM (SELECT NULL AS a, 'b' AS b) t
WHERE (a, b) NOT IN (SELECT NULL, 'b')

?

Thanks, Rafael.

Solution

  • Because (NULL = NULL) <> True

    create table three(
        one integer
        );

INSERT INTO three(one) VALUES(1),(2),(NULL);

SELECT one.one AS one
        , two.one AS two
        , one.one = two.one AS diff
FROM three one
CROSS JOIN three two
        ;

    Result:

    CREATE TABLE
INSERT 0 3
 one | two | diff 
-----+-----+------
   1 |   1 | t
   1 |   2 | f
   1 |     | 
   2 |   1 | f
   2 |   2 | t
   2 |     | 
     |   1 | 
     |   2 | 
     |     | 
(9 rows)

    So, comparing anything to NULL results in NULL, not True or False.

    In your case, you are comparing tuples, not scalars. For tuples it is even worse: if any element of one of the tuples is NULL, the comparison result is neither True nor False.