Don't understand query (JOIN with NULL)

[MDanz]

The query below is correct.  It returns the rows `paul` and `rick` because they have the highest rating of the child rows.  This was solved for me with the below link.  I am having trouble understanding the query.  I understand joins, what i don't understand is `t2.rating is null`? 

 

http://sqlfiddle.com/#!2/97e60/2

 

 

 

 

 select t1.* from mytable t1
        left join mytable t2
        on t1.parentid = t2.parentid and t1.rating < t2.rating
        join mytable parents
        on parents.id = t1.parentid
        where t2.rating is null AND parents.name like '%mike%'

 

`t1.rating < t2.rating` attaches the highest rated values to the LEFT table.  I know `t2.rating is null` means t2.rating is false but i have no idea what it is doing in the query?  Does that mean it removes from t1 where there isn't a match in the LEFT JOIN query?

 

my head hurts... the simpler the explanation the better.

[The Little Guy]

basically that mean their wasn't a related result from t2.rating so it filled it in with nulls

 

and btw null does not equal: false, 0, '', NULL or anything

[fenway]

The query simply lines up each matching parentid row, pairwise -- then it checks to see which rating column is bigger.  Since you want to find the largest one, that's the one where there is no larger value; and in a LEFT JOIN, that's where the rating is NULL.