NOT IN and NULL in multi-column case

When I interview people I usually ask the question like this:

Suppose I have table T ( ID INT, NAME VARCHAR2(10)) and three row in that:

ID NAME
---- ---------
1 ALLEN
2 SCOTT
3 WARD

What is the result of the query

SELECT * FROM T WHERE (ID, NAME) NOT IN ((3, 'ALLEN'), (2,NULL)) ?

I am quite surpised how many people answer "no one row" on this question.
Probably it is the result of the confusion with the fact what the query

SELECT * FROM T WHERE (NAME) NOT IN ('ALLEN', NULL)

returns no rows.

This is the example how the common sense can play a bad joke.

In reality the expression

(ID, NAME) NOT IN ((3 'ALLEN'), (2,NULL))

is the equivalent of

(ID, NAME) != (3, 'ALLEN') AND (ID, NAME) != (2, NULL)

We can decompose this expression deeper so we will have

(ID != 3 OR NAME != 'ALLEN') AND (ID != 2 OR NAME != NULL)

So now it's clear what the result of the estimation for the record

1, ALLEN

is:

(TRUE OR FALSE) AND (TRUE OR NULL) == TRUE AND TRUE == TRUE

The estimation for (2, SCOTT) is

(TRUE OR TRUE) AND (FALSE OR NULL) == TRUE AND NULL == NULL

and (3, WARD) estimated as

(FALSE OR TRUE) AND (TRUE OR NULL) == TRUE AND TRUE == TRUE

So we have the final result

1, ALLEN
3, WARD


You will have "no rows returned" result only in case

NOT IN ((...), (...), (NULL,NULL,...,NULL)) where (NULL,NULL,...,NULL) is the cartige
contained only NULL values.