Louis Davidson looks at a couple edge cases with the STRING_SPLIT function in SQL Server 2016:
But what about the two versions of an empty value? ” (zero-length/empty string) and NULL. My NULL sense told me that the NULL one would return a single row with NULL, and the empty string would return a single empty string row. Of course, I was wrong, and it makes sense why (a row of NULL would be really annoying, especially if you want to use the output as an exclusion list, because A NOT IN (SET(B,NULL)) always returns NULL, not TRUE. )
For example, say the output could include NULL. You could end up with something like the following, where even though the input value of A is not in the NOT IN list, no rows are returned:
Click through for more details.