why "ANY" isn't working properly?

Question

I'm learning SQL using Oracle 10g. I need a query that returns the department with the most employees to use it in a update sentence. I already solved it, but I couldn't figure out why this query won't work:

select deptno
from (select deptno, 
             count(*) num 
        from emp 
       group by deptno)
where not num < any(select count(deptno) 
                      from emp 
                     group by deptno)

It puzzles me more since according to the documentation it should be equivalent and optimized into the following:

select deptno 
  from (select deptno, 
               count(*) num 
          from emp 
         group by deptno )
 where not exists( select deptno, 
                          count(*) 
                     from emp 
                   having count(*) > num 
                    group by deptno)

That one works without errors. The following also work:

select deptno 
  from (select deptno, 
               count(*) num 
          from emp 
         group by deptno)
 where num = (select max(alias) 
                from (select count(deptno) alias 
                        from emp 
                       group by deptno))


select deptno 
  from emp
 group by deptno
 having not count(deptno) < any( select count(deptno) 
                                   from emp 
                                  group by deptno)

Edit. Probably it'll help if I post the return values of the inner selects.

The first select returns:

Dept. Number         Employees
30                   6
20                   5
10                   3

The last one returns (3,5,6)

I checked them individually. It's also weird that if I put the values manually it works as expected and will return 30 as the department with most employees.

select deptno
from (select deptno, 
             count(*) num 
        from emp 
       group by deptno)
where not num < any(6,5,3)

I'm using Oracle 10g 10.2.0.1.0

Last edit, probably. Still don't know what's happening, but the behaviour is as if the last select is returning null somehow. So, even if I remove the ´not´, it still doesn't select anything.

If someone is interested I also found this useful: TSQL - SOME | ANY why are they same with different names? Read the first answer. It's probably better to avoid the use of any/some, all.

Answer 1

Here's a similar example which may clarify things (Standard SQL, can be easily transformed for Oracle):

WITH T
     AS 
     (
      SELECT * 
        FROM (
              VALUES (0),
                     (1), 
                     (2),
                     (NULL)
             ) AS T (c)
     )
SELECT DISTINCT c
  FROM T
 WHERE 1 > ALL (SELECT c FROM T T2);

This returns the empty set, which is reasonable: given the presence of the null in the table, 1 > NULL is UNKNOWN, therefore it is not known whether the value 1 is greater than all values in the set.

However, adding the NOT operator:

 WHERE NOT 1 > ALL (SELECT c FROM T T2);

returns all values in the set, including the null value. At first glance this seems wrong: given that 1 > 2 is FALSE we can say with certainty that the value 1 is not greater than all values in the set, regardless of the null.

However, in this case the NOT is simply flipping the earlier result i.e. the opposite of all no rows is all rows! ;)

Further consider the negated comparison using a column (rather than the literal value 1):

WHERE NOT c > ALL (SELECT c FROM T T2);

This time it returns all rows except for the null value.

Answer 2

Correction (update)

not num < any(select ...)

should be the same as your other queries. You can also try this variation:

num >= ALL(select ...)

but I can't understand why yours is giving wrong results. Perhaps because of the not precedence. Can you trythis instead?:

not ( num < ANY(select ...) )

---

Full queries:

select deptno
from (select deptno, count(*) num from emp group by deptno)
where num >= all(select count(deptno) from emp group by deptno)

and:

select deptno
from (select deptno, count(*) num from emp group by deptno)
where not ( num < any(select count(deptno) from emp group by deptno) )