Mysql LEFT JOIN how it works

Question

Take for example the following table (that use mysql in its documentation) :

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

and then suppose we want to get in output the row with the max price usign the following query

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

Yes it works right it return the last column of the table (the one with the max price).
With this query i don't really understand how it works (not left join) but why we have an article that IS NULL.

I think the same thing happen if we want to do a left join without the intersection so we use :

SELECT * from A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL.<br>

But why we have b.key is null if we always have a value for that column?

Please check this out: http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join Everything will be clear after doing some reading and watching the pictures. — C Cislariu, Feb 26 '16 at 17:28
This picture doens't answer to my question. I asked why not the result of the query, i just know it. — Isky, Feb 26 '16 at 17:30
Here is one of my old answer, this should clear your doubt http://stackoverflow.com/questions/29944271/how-does-left-join-is-null-eliminate-records-which-are-there-in-one-table-and/29944479#29944479 Only difference is the example is with 2 tables and you have one but the logic is same. — Abhik Chakraborty, Feb 26 '16 at 17:39

score 0 · Answer 1 · answered Feb 26 '16 at 17:30

Well, you are actually joining two tables using a comparison: s1.price < s2.price.

So, each row of table 1 is matched with all rows of table 2 where the price is higher. Since a LEFT JOIN is used, it will also return rows from table 1 which do not have a matching row in table 2. And since we are looking for the highest price, this row is exactly what we are looking for, the one with the highest price which doesn't have a matching row in table 2 (s2.article = NULL) with an even higher one.

Mysql LEFT JOIN how it works

1 Answers1