NOT IN predicate
Let’s take a look at another example, which I borrowed from Joe Selko . Its idea consists of using NOT IN predicate (<list of values including NULL>).
And again, to make it possible for you to check the justice of the discussion on the site, let’s artificially add NULL values to the query result:
I specially chose year 1915 to make the resulting set smaller. Here it is:
And now we write a query which must return all vessels, the launch year is not in the values set of the year column:
It seems as we should’ve received Hiei and Kongo ships, as their launch year is known and it’s not 1915. But again we get an empty resulting set.
Let’s evaluate the predicate value for the first of the ships – Hiei (it will be the same for the other vessel). So,
I omitted another NULL value for short. The last predicate can be replaced by the following:
and that is equivalent to
The last expression is always UNKNOWN, so we can rewrite the predicate:
Therefore, the whole expression will be UNKNOWN as well, because the first comparison gives TRUE. If the first comparison was FALSE (for year 1915), the result would be FALSE.
So, we can make a conclusion that, when having NULL values in data set, NOT IN predicate in WHERE clause will always give an empty resulting set.
Finally, it should be said that if you do a vertical fragmentation of a table using some threshold column value, which allows NULL values, the joining of fragments like