CODEWITHSUNDEEP
mysqlleft-join
mercy
mercy

Reputation: 151

using left join along with 'IS NULL'

I came across this query to get a row having max value. Can you explain how this works

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
WHERE s2.article IS NULL;

Dump : 

CREATE TABLE `shop` (
`article` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
`dealer` char(20) NOT NULL DEFAULT '',
`price` double(16,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`article`,`dealer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shop` VALUES 
(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);

Upvotes: -1

Views: 89

Answers (2)

persona
persona

Reputation: 108

Why

WHERE s2.article IS NULL

is used, from the MySQL reference:

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.article value is NULL

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

The query performs a self join based on the predicate:

s1.price < s2.price

So, each record of the table is joined to any record having a greater price.

The predicate of the WHERE clause:

WHERE s2.article IS NULL

is true when no such record exists. Hence the query returns the record having the greatest price.

Upvotes: 2

Related Questions