NULL
ValuesDISTINCT
NULL
Values
NULL
represents the absence of a value or an unknown value.
The fundamental principle is that NULL is not equal to anything, including itself, and most operations involving NULL result in NULL.NULL
value cannot be compared to any other value using standard comparison operators (=, !=, <, >, etc.).
The NULL
value is neither equal, nor greater than, nor less than, nor different from any other value, even if the other value is also NULL
.NULL
evaluate to UNKNOWN, which is treated as FALSE in WHERE clauses and other boolean contexts.SELECT 1 AS v1 FROM DUAL WHERE NULL = NULL OR NULL != NULL OR NULL = 1 OR NULL != 1Query result :
v1 --
NULL
value cannot be added, subtracted, multiplied, or divided by any other value, even if the other value is also NULL
.NULL
propagates the NULL value, meaning the result is always NULL
.
This includes concatenation operations as well.SELECT NULL + NULL AS v1 , NULL + 1 AS v2 FROM DUALQuery result :
v1 v2 ------ ------ (null) (null)
NULL
or not, you must use the predicates IS NULL
and IS NOT NULL
.
These are the only reliable ways to check for NULL values in Oracle SQL.SELECT 1 AS v1 FROM DUAL WHERE NULL IS NULLQuery result :
v1 -- 1Example 2 :
SELECT 1 AS v1 FROM DUAL WHERE 1 IS NOT NULLQuery result :
v1 -- 1
COUNT
and other functions.COUNT
returns 0 when all values are NULL or no rows exist or when passing a literal NULL value as an argument.SUM
, MIN
, MAX
, AVG
)
return NULL when all values are NULL or no rows exist or when passing a literal NULL value as an argument.a.v1
is NULL
for all rows in the query result.SELECT COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1) FROM ( SELECT NULL AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL ) aQuery result :
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1) ----------- --------- --------- --------- --------- 0 (null) (null) (null) (null)
SELECT COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1) FROM ( SELECT NULL AS v1 FROM DUAL WHERE 1=0 ) aQuery result :
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1) ----------- --------- --------- --------- --------- 0 (null) (null) (null) (null)
NULL
value as an argument to these functions !!SELECT COUNT(NULL), SUM(NULL), MIN(NULL), MAX(NULL), AVG(NULL) FROM ( SELECT 1 AS v1 FROM DUAL ) aQuery result :
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1) ----------- --------- --------- --------- --------- 0 (null) (null) (null) (null)
a.v1
is NULL
for some rows in the query result.a.v1
is NULL
.SELECT COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1) FROM ( SELECT 1 AS v1 FROM DUAL UNION ALL SELECT 2 AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL ) aQuery result :
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1) ----------- --------- --------- --------- --------- 2 3 1 2 1.5
COUNT
function is an exception to this rule,
as it does not consider the value of the argument,
as long as the value is not NULL
, of course.COUNT
function can also take the character "*" as an argument.SELECT COUNT(10), SUM(10), MIN(10), MAX(10), AVG(10) FROM ( SELECT NULL AS v1 FROM DUAL UNION ALL SELECT 1 AS v1 FROM DUAL ) aQuery result :
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1) ----------- --------- --------- --------- --------- 2 20 10 10 10
IN
and NOT IN
operators have complex behavior when dealing with NULL values.IN
expression IN (expression_list)
IN
operator performs multiple equality comparisons and returns TRUE if any comparison succeeds.
Since NULL comparisons always return UNKNOWN (treated as FALSE), NULL values in either the expression or the list can affect the result.expression_list
" can be a list of values or a subquery that can return multiple rows (with a single column).expression
" is compared with each element in the "expression_list
".expression
" is equal to at least one element in "expression_list
".expression
" is different from each element in "expression_list
".expression
" is NULL
, the list of elements is empty, or the elements in the subquery are all NULL
.SELECT 1 AS v1 FROM DUAL WHERE 1 IN (1,2,NULL)
SELECT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL ) a WHERE a.v1 IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b )
v1 -- 1
SELECT 1 AS v1 FROM DUAL WHERE 1 IN (NULL,NULL)
SELECT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL ) a WHERE a.v1 IN ( SELECT b.v2 FROM ( SELECT NULL AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b )
SELECT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL ) a WHERE a.v1 IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL WHERE 1=0 ) b )
v1 --
SELECT 1 AS v1 FROM DUAL WHERE NULL IN (1,2,NULL)
SELECT a.v1 FROM ( SELECT NULL AS v1 FROM DUAL ) a WHERE a.v1 IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b )
v1 --
NOT IN
expression NOT IN (expression_list)
NOT IN
operator is particularly tricky with NULL values.
It requires that the expression be different from ALL values in the list.
If any comparison results in UNKNOWN (due to NULL), the entire condition becomes FALSE.expression_list
" can be a list of values or a subquery that can return multiple rows (with a single column).expression
" is compared with each element in the "expression_list
".expression
" is different from each element in "expression_list
".expression
" is equal to at least one element in "expression_list
".expression
" is NULL
or at least one element in "expression_list
" is NULL
.SELECT 5 AS v1 FROM DUAL WHERE 5 NOT IN (1,2,NULL)
SELECT a.v1 FROM ( SELECT 5 AS v1 FROM DUAL ) a WHERE a.v1 NOT IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b )
v1 --
SELECT NULL AS v1 FROM DUAL WHERE NULL NOT IN (1,2,NULL)
SELECT a.v1 FROM ( SELECT NULL AS v1 FROM DUAL ) a WHERE a.v1 NOT IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b )
v1 --
SELECT a.v1 FROM ( SELECT 5 AS v1 FROM DUAL ) a WHERE a.v1 NOT IN ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL WHERE 1=0 ) b )Query result :
v1 -- 5
EXISTS
and NOT EXISTS
operators behave differently from IN
and NOT IN
when dealing with NULL values.
These operators only care about whether the subquery returns any rows, not the actual values returned.EXISTS
EXISTS (subquery)
EXISTS
operator returns TRUE if the subquery returns at least one row, regardless of whether those rows contain NULL values.
The actual data values in the subquery result are irrelevant.NULL
values only affects the subquery: as long as the subquery returns a row,
the EXISTS
operator returns true; otherwise, it returns false.SELECT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL ) a WHERE EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b WHERE b.v2 = a.v1 )Query result:
v1 -- 1
SELECT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL ) a WHERE EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL WHERE 1=0 ) b WHERE b.v2 = a.v1 )
SELECT a.v1 FROM ( SELECT NULL AS v1 FROM DUAL ) a WHERE EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b WHERE b.v2 = a.v1 )
v1 --
NOT EXISTS
NOT EXISTS (subquery)
NOT EXISTS
operator returns TRUE if the subquery returns no rows.
Unlike NOT IN
, the presence of NULL values in the subquery does not affect the result - only the row count matters.NOT EXISTS
operator is completely different from the NOT IN
operator when it comes to NULL
values;
the presence or absence of NULL
values only affects the subquery: as long as the subquery does not return any rows,
the NOT EXISTS
operator returns true; otherwise, it returns false.SELECT a.v1 FROM ( SELECT 5 AS v1 FROM DUAL ) a WHERE NOT EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b WHERE b.v2 = a.v1 )Query result:
v1 -- 5
SELECT a.v1 FROM ( SELECT NULL AS v1 FROM DUAL ) a WHERE NOT EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL UNION ALL SELECT 2 AS v2 FROM DUAL UNION ALL SELECT NULL AS v2 FROM DUAL ) b WHERE b.v2 = a.v1 )Query result:
v1 ------ (null)
SELECT a.v1 FROM ( SELECT 5 AS v1 FROM DUAL ) a WHERE NOT EXISTS ( SELECT b.v2 FROM ( SELECT 1 AS v2 FROM DUAL WHERE 1=0 ) b WHERE b.v2 = a.v1 )Query result:
v1 -- 5
SELECT NULL AS v1 FROM DUAL UNION SELECT NULL AS v1 FROM DUALQuery result:
v1 ------ (null)
SELECT NULL AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUALQuery result:
v1 ------ (null) (null)
SELECT NULL AS v1 FROM DUAL INTERSECT SELECT NULL AS v1 FROM DUALQuery result:
v1 ------ (null)
SELECT NULL AS v1 FROM DUAL MINUS SELECT NULL AS v1 FROM DUALQuery result:
v1 ------
DISTINCT
DISTINCT
operator follows the same NULL handling rules as set operations.
Multiple NULL values are considered identical and are reduced to a single NULL value in the result set.SELECT DISTINCT a.v1 FROM ( SELECT 1 AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL ) aQuery result:
v1 ------ 1 (null)
GROUP BY
and ORDER BY
clauses treat NULL values consistently with set operations.
NULL values are considered equal to each other for grouping purposes and are sorted together in ordering operations.GROUP BY
.SELECT a.v1, COUNT(1) AS nbr FROM ( SELECT 1 AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL UNION ALL SELECT NULL AS v1 FROM DUAL ) a GROUP BY v1 ORDER BY v1 DESCQuery result:
v1 nbr ------ ------ (null) 2 1 1
Operation | NULL Scenario | Result | Returns Rows |
---|---|---|---|
expr = NULL |
Any comparison with NULL | Always FALSE | No |
expr IS NULL |
Testing for NULL values | TRUE if expr is NULL | Yes (if NULL) |
expr IN (list) |
expr is NULL | Always FALSE | No |
expr IN (list) |
List contains only NULLs | Always FALSE | No |
expr NOT IN (list) |
expr is NULL | Always FALSE | No |
expr NOT IN (list) |
List contains any NULL | Always FALSE | No |
EXISTS (subquery) |
Subquery returns NULLs | TRUE if any rows returned | Yes (if rows exist) |
NOT EXISTS (subquery) |
Subquery returns NULLs | FALSE if any rows returned | No (if rows exist) |
COUNT(column) |
Column contains NULLs | Ignores NULL values | Returns count of non-NULLs |
SUM/MIN/MAX/AVG(column) |
All values are NULL | Returns NULL | Returns NULL result |
DISTINCT |
Multiple NULL values | Treats NULLs as equal | Returns single NULL |
GROUP BY |
NULL values in grouping column | Groups all NULLs together | Creates NULL group |
Set Operations |
UNION/INTERSECT/MINUS with NULLs | Treats NULLs as equal | Handles NULLs consistently |