Why null values are not ranking same in mysql?

Question

Yesterday I asked a question about ranking students 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.

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 ?

score 4 · Accepted Answer · answered Jan 07 '16 at 12:16

4

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).

answered Jan 07 '16 at 12:16

axiac

68,258
9
99
134

impossible to argue with this answer! I think you need to edit the wikipedia post. – davejal Jan 07 '16 at 12:23
Well, for those patient enough to read, the Wikipedia page also says (in the third paragraph): *"SQL null is a state (unknown) and not a value."*. – axiac Jan 07 '16 at 13:25

dnoeth · Answer 2 · 2016-01-07T12:17:11.767

2

You compare @points = points which results in @points=NULL.

But any comparison to NULL results in UNKNOWN:

value= NULL -> UNKNOWN
value<>NULL -> UNKNOWN
value< NULL -> UNKNOWN
value> NULL -> UNKNOWN

Even NULL=NULL results in UNKNOWN.

Of course there are exceptions:

DISTINCT and GROUP BY consider NULLs equal.

edited Jan 07 '16 at 12:17

answered Jan 07 '16 at 12:06

dnoeth

59,503
4
39
56

Hi, then why I am not recieving an error such as you can't compare null with null ? – Anar Bayramov Jan 07 '16 at 12:08
1

@Rodrane: Because you're not using a literal NULL, but a parameter and a column. – dnoeth Jan 07 '16 at 12:12
1

@Rodrane This is not error. Welcome to **[3-valued logic](https://en.wikipedia.org/wiki/Ternary_logic)**. – Lukasz Szozda Jan 07 '16 at 12:12
Great catch on the exceptions! – davejal Jan 07 '16 at 12:26

score 2 · Answer 3 · answered Jan 07 '16 at 12:10

2

NULL does not have a value - it is a state of non-existence. So any results based on NULL is non-deterministic. You should EXPLICITLY deal with the possibility of NULL values.

answered Jan 07 '16 at 12:10

Paul Whitfield

48
9

score 1 · Answer 4 · answered Jan 07 '16 at 12:43

Boolean comparison return true, false, or NULL, and NULL is generally treated as false. You can easily modify your query to use NULL-safe equality:

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;

Why null values are not ranking same in mysql?

4 Answers4

Linked