CODEWITHSUNDEEP
mysqljoin
Rdesmond
Rdesmond

Reputation: 1211

How using JOIN to sort table works?

I'm going through the mysql docs, and under the common queries section, they mention how to get the maximum column value of a table by using a LEFT JOIN. Here's the table:

SELECT * FROM shop;

+---------+--------+-------+
| 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 here's the 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;

There's several things I can't make heads or tails of.

First, without any LIMIT 1 statement or ordering (which defeats the purpose of the JOIN), I don't understand how it can only return one row (assuming LEFT JOIN is a LEFT OUTER JOIN that returns all rows in the left table according to w3 Schools).

Second, I don't understand the semantics of s1.price < s2.price. Is this saying, "select all rows from s2 where the statement s1.price < s2.price is true (and if so, then w3 schools link above isn't true for mysql)? I.e. LEFT JOIN is a LEFT INNER JOIN?

Finally, the WHERE s2.article IS NULL makes absolutely no sense to me since each record in the table has an article attribute (especially since there's no ambiguous 0000 article).

Upvotes: 1

Views: 66

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

The joining condition s1.price < s2.price means that each s1 row will join with every s2 row that has a higher price.

This means that for the row in the table s1 with the highest price (19.95) no s2 rows will match. Because it is in an outer join the unmatched s1 row is returned anyway, but all the s2 columns will be set to null.

The where s2.article is null then discards all the rows except that one. You may find it informative to inspect the results before that stage with

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

You will see the 1.25 s1 row joins with 6 s2 rows, the 10.99 row with one, and the 19.95 one none at all.

Note it is not guaranteed to return only one row, if multiple rows are tied for the highest price all of those will be returned.

Upvotes: 2

Related Questions