Subquery in From Statement

Subquery in From statement

this is used the most frequently! It's normally used to add Select to an existing table.

Q. Does a user have more likes on average if they have more points?

1. To see the average of likes by user,

   select user_id, round(avg(likes), 1) as avg_likes from checkins 
   group by user_id
   

   

   avg() and count() cannot be used together. e.g. avg(count(X))

2. Make a frame for the data

   select user_id, point from point_users
   

   

   if the table no.1 and the table no.2 can be joined, it shows the desired outcome.

3. Use Inner join

   select pu.user_id, pu.point, a.avg_likes from point_users pu 
   inner join (
    select user_id, round(avg(likes), 1) as avg_likes from checkins 
    group by user_id 
   ) a on pu.user_id = a.user_id
   

   

The process of running the code; Select in the subquery- Consider it as a new table - Run Select outside of the subquery.

Why the other way of inner join does not work?

• Even though the below code works,

SELECT pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (

select c.user_id, round(AVG(likes), 1) as avg_likes from checkins c 
group by c.user_id 

) a on pu.user_id = a.user_id

The other way of Inner join does not work.

select c.user_id, round(AVG(c.likes), 1) as avg_likes from checkins c 
group by c.user_id 
inner join (

SELECT pu.user_id, pu.point from point_users pu

) a on c.user_id = a.user_id

• Why? It's because of the order. MySQL reads inner join first before group by or where, so inner join should be before group by.