I have a query which is following:

select model_name, price 
from   `car` v
where ( select count(*) 
        from   `car` vc
        where  vc.`price` > v.`price`
      ) < 5

with a data set:

model_name  price
a            1
b            2
c            3
d            4
e            5
f            6
g            7
h            8
i            9
k            10

the upper query fetches the result as follows:

model_name  price
f            6
g            7
h            8
i            9
k            10

The main problem here is of understanding the logical flow behind this query. This logical flow which I understand was just like cross join the sub query will work on the back end like this:

VC_name     VC_Price    counts
a             1           0
b             2           1
c             3           2
d             4           3
e             5           4
f             6           5
g             7           6
h             8           7
i             9           8
k             10          9

If we take, back end calculation like this it should return counts which are less then 5, which has model_name a, b, c, d, e but instead this query outputs f, g, h, i, k.

Now if we proceed with how normal query works subquery would return only one record because of count, and after that if the condition is true it should return all rows and if false it should just return NULL. For example if subquery returns 4 then 4<5, true it should return all rows but the output is all different. The main question about this is how MYSQL is entertaining this query on backend, what would be the flow.

I hope I was able to explain my question.

Thanks in advance.

CodePudding user response：

the sub query will work on the back end like this:
...
where vc.price > v.price

Close, but the actual counts are reversed because your subquery is checking for cars with a greater price. Conceptually, it's doing this:

select v.model_name, v.price, count(*)-1
from   car v cross join car vc
where  vc.`price` >= v.`price`
group by v.model_name, v.price

Which produces these results:

db<>fiddle here

Original query: ...

    where ( select count(*) 
            from   `car` vc
            where  vc.`price` > v.`price`
          ) < 5

So your query ends up returning the most expensive cars (f,g,h,i,k) because there are fewer cars with a greater price.

CodePudding user response：

Thank you for your response, Well I was asking for the flow of this Query. Studied some topics and just came to know the whole flow for this.

As SQL is a sequential language it will check the condition on every row, following query will perform like this:

On the fist step

select model_name, price 
from   `car` v
where ( select count(*) 
        from   `car` vc
        where  vc.`price` > v.`price` /* value for v.price = 1 and for vc.price
                                      it would iterate whole data from 1 to 10.
                                      So there will 9 values which are greater
                                      then 1. This is just working as loop. So 
                                      returned count(*) would be 9 which is 
                                      greater then 5, resulting in false 
                                      condition. So model_name 'a' will not be 
                                      returned in main select*/ 
      ) < 5

So this Query will iterate one by one for v.price, for every row of v.price it will iterate and check whole data of vc table. For example if v.price = 2 then there will 8 counts which are greater then 2. Until it reaches v.price = 10 then the count greater then 10 would be 0. In the end all those model_names which have counts less then 5 will be returned.

Once again Thankyou for your response.