NULL behavior with Comoperator like ALL in Oracle SQL

SELECT * FROM   hr.NullValueCheck  

ID      Name
1   abc
2   abc
3   bcd
4   cde

https://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql

Query 1 :

SELECT *
FROM   hr.NullValueCheck  
where id > All (SELECT NULL  FROM DUAL )

Nothing is coming.

But for below quesry. All records are coming while subquesry is returning is NULL same as like above query (SELECT NULL FROM DUAL )

Query 2:

SELECT *
FROM   hr.NullValueCheck  
where id > All (SELECT id from  hr.NullValueCheck where id = 5)

Please explain me why Query 1 is returning No Records but Query 2 is returning all records.

As per my knowledge Query 1 should also return all records.

Answer

NULL is different from an empty set.

The first example is saying: “select all rows where the id is greater than all values of NULL“. Or more simply, “where id is greater than ‘NULL’`.

In SQL, 'NULL' generally has the semantics of “not known”. If you don’t know the value, then you don’t know if a given id is larger. Hence, no rows are returned.

In the second example, instead has an empty set for comparison. An empty set is not NULL. Obviously, any number is greater than all numbers in an empty set. Hence, all rows are returned.