Search code examples
mysqlsqldatabasenullranking

Why null values are not ranking same in mysql?

Yesterday I asked a question about ranking students by their points.

[Rank users in mysql by their points ]

I actually solved my problem with the help I've recieved from other question (thanks to everyone who helped me).
Meanwhile I figured out something accidently. If I try to rank my students by their points and if points column was empty (NULL) my query didn't gave an error also it sorted ranks like 1-2-3-4 but all values were NULL

this is the query I've tried 

select er.*,
       (@rank := if(@points = points, 
                    @rank, 
                    if(@points := points,    
                       @rank + 1, 
                       @rank + 1                       
                      )
                   )                  
       ) as ranking
from examresults er cross join
     (select @rank := 0, @points := -1) params
order by points desc;

and this is the result.

enter image description here

So I wonder arent NULL values are the same ? isnt this query should gave rank 1 for every user in my database ? why it ranks null values incrementally ?

Solution

  • Many people think of NULL as meaning no value or non-existent. Even Wikipedia explains it this way.

    Think of it as unknown value and everything will make more sense.

    One cannot compare two unknown values because, well, they are not known. They are not equal but they are also not different. None of them is greater than the other.

    The result of comparison of two unknown values is also an unknown value (i.e. NULL).