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.

[smantscheff]

Consider this:

drop table if exists mytable;
CREATE TABLE mytable (
     id int, name char(20), parentid int, position int, rating int
    );
INSERT INTO mytable VALUES
(1, 'mike', 1, 1, 6),
(2, 'dave', 1, 2, 5),
(3, 'paul', 1, 2, 7),
(4, 'john', 1, 2, 3),
(5, 'mick', 5, 1, 8),
(6, 'owen', 5, 2, 2),
(7, 'rick', 5, 2, 9),
(8, 'jaye', 5, 2, 3);

 select t1.*,t2.*, parents.* 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 parents.name rlike 'mike|mick'

+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
| id   | name | parentid | position | rating | id   | name | parentid | position | rating | id   | name | parentid | position | rating |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
|    1 | mike |        1 |        1 |      6 |    2 | dave |        1 |        2 |      5 |    1 | mike |        1 |        1 |      6 |
|    1 | mike |        1 |        1 |      6 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    2 | dave |        1 |        2 |      5 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    3 | paul |        1 |        2 |      7 |    1 | mike |        1 |        1 |      6 |    1 | mike |        1 |        1 |      6 |
|    3 | paul |        1 |        2 |      7 |    2 | dave |        1 |        2 |      5 |    1 | mike |        1 |        1 |      6 |
|    3 | paul |        1 |        2 |      7 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    4 | john |        1 |        2 |      3 | NULL | NULL |     NULL |     NULL |   NULL |    1 | mike |        1 |        1 |      6 |
|    5 | mike |        5 |        1 |      8 |    6 | owen |        5 |        2 |      2 |    5 | mike |        5 |        1 |      8 |
|    5 | mike |        5 |        1 |      8 |    8 | jaye |        5 |        2 |      3 |    5 | mike |        5 |        1 |      8 |
|    6 | owen |        5 |        2 |      2 | NULL | NULL |     NULL |     NULL |   NULL |    5 | mike |        5 |        1 |      8 |
|    7 | rick |        5 |        2 |      9 |    5 | mike |        5 |        1 |      8 |    5 | mike |        5 |        1 |      8 |
|    7 | rick |        5 |        2 |      9 |    6 | owen |        5 |        2 |      2 |    5 | mike |        5 |        1 |      8 |
|    7 | rick |        5 |        2 |      9 |    8 | jaye |        5 |        2 |      3 |    5 | mike |        5 |        1 |      8 |
|    8 | jaye |        5 |        2 |      3 |    6 | owen |        5 |        2 |      2 |    5 | mike |        5 |        1 |      8 |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+

Now add the "t2 = null" test to the where condition.

select t1.*,t2.*, parents.* 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 rlike 'mike|mick'

+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
| id   | name | parentid | position | rating | id   | name | parentid | position | rating | id   | name | parentid | position | rating |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
|    4 | john |        1 |        2 |      3 | NULL | NULL |     NULL |     NULL |   NULL |    1 | mike |        1 |        1 |      6 |
|    6 | owen |        5 |        2 |      2 | NULL | NULL |     NULL |     NULL |   NULL |    5 | mike |        5 |        1 |      8 |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+

The left join clause creates a virtual row in the right table which has only null values. Then it builds the cartesian product of the left and the right table and filters according to the where clause.

The left join clause "t1.rating > t2.rating" compares all rows of the left table with all rows of the right table and returns only those where this clause is either true or null. It is false for all records where the t2.rating is equal or higher than t1.rating, so it leaves only the rows where there is a lower t2.rating. Then the "t2.rating is null" clause filters out the one row where a match in the right table with a lower rating could not be found - the minimum - and returns this row.
A quite tricky way to find this result. This would have been easier:

select p.name,t.* from mytable t, mytable p
where t.parentid=p.id 
and p.name rlike "mike|mick"
and (t.parentid,t.rating) in
(select parentid, min(rating) 
from mytable 
group by parentid
)
)

+------+------+------+----------+----------+--------+
| name | id   | name | parentid | position | rating |
+------+------+------+----------+----------+--------+
| mike |    4 | john |        1 |        2 |      3 |
| mike |    6 | owen |        5 |        2 |      2 |
+------+------+------+----------+----------+--------+

[MDanz]

thanks... i understand. how did you print the tables? It is much easier to understand when i can visually see the data.

[smantscheff]

The tables are the output of the mysql command line client.