Is this a bug in Analytical Functions? NULL is supposed to be UNEQUAL to NULL?

While presenting the 7Up Training – upgrading experienced Oracle developers from their Oracle 7 base of knowledge and experience ("still writing 7 backwards compatible code against a 10g database") to 10g, we ran into a funny little example with the Oracle Analytical SQL Functions – that also applies to standard GROUP BY operations by the way – that suggests that NULL == NULL in some circumstances. To be fair: it feels (intuitively) correct. but theoretically (what do you say Toon??) it is a little fishy I think.

....
 

The situation is very simple: I have updated my EMP table to have several employees without department. Then I queried to find out for each employee his name, department, salary and the percentage of the total salary sum within the department allocated to the employee. The query is straightforward:

select ename
, deptno
, sal
, ratio_to_report(sal)
over ( partition by deptno)
sal_percentage
from emp
order
by 2, 4

The data in EMP shows two employees without Department (i.e. their DEPTNO is NULL). The results of this query show the two employees without department – MILLER and KING – as having 20 respectively 80 percent of the total salary sum in their department (the partition of all employees with the same DEPTNO). However, their DEPTNO is NULL and since NULL is never equal to NULL, there are no other employees with the same DEPTNO. Or are there?

ENAME          DEPTNO        SAL SAL_PERCENTAGE
---------- ---------- ---------- --------------
CLARK 10 2695 1
SMITH 20 800 .071604386
ADAMS 20 1100 .09845603
SCOTT 20 3000 .268516447
FORD 20 3000 .268516447
JONES 20 3272.5 .292906691
JAMES 30 950 .09808983
MARTIN 30 1250 .129065565
WARD 30 1250 .129065565
TURNER 30 1500 .154878678
ALLEN 30 1600 .165203924
BLAKE 30 3135 .323696438
MILLER 1300 .200896307
KING 5171 .799103693

On further analysis – something I apparently never actively realized – the classic GROUP BY does the exact same thing:

 select deptno
, count(*)
from emp
group
by deptno

The results also suggest an equality between NULL…

DEPTNO COUNT(*)
-------- ----------
30 6
2 <<<<-------
20 5
10 1
 

13 Comments

  1. Marco Gralike October 14, 2006
  2. Patrick Sinke October 14, 2006
  3. Mr. Ed October 14, 2006
  4. Marco Gralike October 14, 2006
  5. Marco Gralike October 14, 2006
  6. Marco Gralike October 14, 2006
  7. Marco Gralike October 14, 2006
  8. Marco Gralike October 14, 2006
  9. Marco Gralike October 14, 2006
  10. Paweł Barut October 13, 2006
  11. Jurgen Kemmelings October 13, 2006
  12. Patrick Sinke October 13, 2006
  13. Harm Verschuren October 13, 2006