Why is empty subquery in SQL IN sometimes treated as null

Question

I noticed the following strange behavior in SQL Server

SELECT *
FROM Tbl
WHERE col NOT IN ((SELECT 1 WHERE 1 = 2))

returns all records (i.e. the subquery result is the empty set) while

SELECT *
FROM Tbl
WHERE col NOT IN (1, (SELECT 1 WHERE 1 = 2))

return no records (i.e. the subquery result is null)

Got the same result in DBFiddle with postgresql and mysql. Sqlite returns no records with both queries because of the extra parenthesis.

Am I thinking completely wrong here?

Could not find anything in the documentation.

The background is I have a method to convert @Param to @Param1, @Param2, @Param3... where the number of parameters come from a sequence.

I want the method to also work for the empty sequence and in that case I replace with (SELECT null WHERE 1 = 2) but it doesn't work when the input is NOT IN (1, @Param)

Is there some other solution to this?

Here is the fiddle code

CREATE TABLE Tbl (col int NOT NULL);
INSERT INTO Tbl VALUES (1);
INSERT INTO Tbl VALUES (2);

I expected the second query to return "2"

Answer 1

The IN syntax is

test_expression [ NOT ] IN   
    ( subquery | expression [ ,...n ]  
    )   

In your first example you are using the subquery route.

In the second example you are providing a list of expression. These are scalar values. A sub query is only accepted in this context if it can be coerced to a scalar value (returns 0 or 1 rows)

It is usual that an empty result set will return NULL when used as a scalar value.

You also see this with

SELECT 1, (SELECT 1 WHERE 1 = 2)

test_expression NOT IN(NULL) returns no rows.

This is the exact opposite behaviour to test_expression NOT IN (empty_set) (returns every row including ones where test_expression is NULL)

You can combine the values with UNION ALL to keep it as a sub query and prevent an empty set being coerced to NULL

SELECT *
FROM   Tbl
WHERE  col NOT IN (SELECT 1 WHERE  1 = 2
                   UNION ALL
                   SELECT 1)