sql where score >= s.score

I have a question about sql. I have a question looks like this.

 +----+-------+
 | Id | Score |
 +----+-------+
 | 1  | 3.50  |
 | 2  | 3.65  |
 | 3  | 4.00  |
 | 4  | 3.85  |
 | 5  | 4.00  |
 | 6  | 3.65  |
 +----+-------+

The table is called 'Scores' and after ranking the score here, it will look like this,

 +-------+------+
 | Score | Rank |
 +-------+------+
 | 4.00  | 1    |
 | 4.00  | 1    |
 | 3.85  | 2    |
 | 3.65  | 3    |
 | 3.65  | 3    |
 | 3.50  | 4    |
 +-------+------+

Here is a sample answer but I am confused about the part after WHERE.

select 
    s.Score, 
    (select count(distinct Score) from Scores where Score >= s.Score) 
 Rank
 from Scores s
 order by s.Score Desc;

This Score >= s.Score is something like Score column compare with itself. I totally feel confused about this part. How does it work? Thank you!

Solution

This is known as a dependent subquery (and can be quite inefficient). A dependent subquery - basically means it cannot be turned into a join because it "depends" on a specific value - runs for every result row in the output for the specific "dependent" values. In this case each result-row already has a "specific" value of s.Score.

The 'Score' in the dependent subquery refers to the original table and not the outer query.

It may be more clear with an additional alias:

select 
  s.Score, 
  (select count(distinct other_scores.Score)
   from Scores other_scores
   where other_scores.Score >= s.Score) Rank -- value of s.Score is known
                                             -- and placed directly into dependent subquery
from Scores s
order by s.Score Desc;

"Modern" SQL dialects (including MySQL 8.0+) provide "RANK" and "DENSE_RANK" Window Functions to answer these sorts of queries. Window Functions, where applicable, are often much faster than dependent queries because the Query Planner can optimize at a higher level: these functions also have a tendency to tame otherwise gnarly SQL.

The MySQL 8+ SQL Syntax that ought to do the trick:

select 
  s.Score, 
  DENSE_RANK() over w AS Rank
from Scores s
window w as (order by Score desc)

There are also various work-abouts to emulate ROW_NUMBER / Window Functions for older versions of MySQL.