3

The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle).

I have the following SQL:

SELECT 1
FROM dual
WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

which returns an empty set and 1 respectively. When we substitute some other value for NULL, we got the exact same result.

SELECT 1
FROM dual
WHERE 33 IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE 33 NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

So how does the comparison between NULL and the empty result set work here?

5

Lets see what happens with these two conditions:

WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

since the subquery yields an empty table, we can write them in pseudocode:

WHERE NULL IN (<empty_table>);

WHERE NULL NOT IN (<empty_table>);

So the first asks whether some (NULL) value is IN an empty table. The result of such condition is always FALSE, independent on whether the value is null or not, since the table is empty.

Applying the same reasoning in the second condition, it is always TRUE, again independent of the value.

It's easy to confirm in Postgres what the result value of these conditions are, as we have a boolean type. See dbfiddle.uk, where the first shows f (FALSE) and the second shows t (TRUE).


As a result of the above, when you run your two queries, they become:

SELECT 1
FROM dual
WHERE FALSE ;

giving an empty result (correct, since the WHERE condition is FALSE)
and

SELECT 1
FROM dual
WHERE TRUE ;

giving one row (correct again).

0

This is a problem with the so called "three-valued logic" which is unique to SQL. Unlike other programming languages that work with TRUE and FALSE, SQL also has UNKNOWN (NULL). And the way different elements of SQL treat an UNKNOWN is inconsistent.

ON, WHERE and HAVING all consider UNKNOWN as FALSE. An empty result set is considered NULL by SQL. Therefore the NOT IN is looking for a false statement, and since WHERE interprets NULL as FALSE, you receive your output. Comparing two NULL values also returns UNKNOWN.

On the other hand, CHECK constraints considers NULL as TRUE. For example, if you were to create a CHECK constraint on sales being higher than zero, NULL > 0 evaluates to UNKNOWN but the rule is that a constraint must not evaluate to FALSE, so it ends up as TRUE. (edited based on Lennart's comment).

Detailed explanation:

SELECT 1
WHERE NULL IN (SELECT 1 WHERE 1 = 0);

(SQL Server syntax)

Returns an empty set, and since an empty data set is considered NULL by SQL, what you're actually doing is:

SELECT 1
WHERE NULL IN (NullSet/EmptySet);

To be more specific, what you're comparing it to is a table of all rows = NULL

https://www.oreilly.com/library/view/sql-and-relational/9781449319724/ch12s07.html

If a row subquery evaluates to an empty table, that empty table is coerced to a row of all nulls.

You also have the math part of it:

http://www.solving-math-problems.com/null-set-empty-set.html

When comparing two NULLs the result is always UNKNOWN, and WHERE statements considers UNKNOWN as FALSE.

Therefore, in your first code:

SELECT 1
WHERE NULL IN (NullSet/EmptySet);

Your WHERE statement is basically checking if NULL exists inside a NULL Set, and since the comparison of NULL is UNKNOWN and UNKNOWN is considered as FALSE, you get no result.

But here:

SELECT 1
WHERE NULL NOT IN (NULL);

You're saying WHERE NULL does not exist inside a NULL Set. Again, since UNKNOWN is considered as FALSE, you get an output.

This also explains why when you compared:

SELECT 1
FROM dual
WHERE 33 NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

You received an output. Because 33 is not in an element inside a NULL Set/Empty Set.

  • I don’t think „NULL in ()“ triggers this, it happens when you compare against null, but since there is no „is any in null“ syntax the IN must be able to deal with NULL pivots. – eckes Aug 1 '19 at 19:30
  • I am genuinely confused. From what I am reading in the top answer is practically the same as what I have described but (admittedly) better explained. He is getting what he is getting due to comparing a NULL with empty set (which is considered as false by WHERE) as the above answer also mentions. – Chessbrain Aug 1 '19 at 20:55
  • Comparing (with „IN“) anything with a empty set is false, it’s not related to comparing null (and it’s UNKNOWN property )to anything. – eckes Aug 1 '19 at 20:57
  • 1
    And as far as my knowledge goes, an empty set is considered NULL by SQL (which is stated in my answer). If the idea of empty data set considered as null in SQL is wrong, I am more than happy to edit my answer to remove that incorrect info. Or delete my answer as a whole. – Chessbrain Aug 1 '19 at 20:58
  • To add to this: oreilly.com/library/view/sql-and-relational/9781449319724/… Where one point states " If a row subquery evaluates to an empty table, that empty table is coerced to a row of all nulls." – Chessbrain Aug 1 '19 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.