Database Reference
In-Depth Information
To obtain the same result with a single statement, use a subquery in the
FROM
clause that
retrieves the same rows contained in the temporary table:
SELECT
artist
.
name
,
painting
.
title
,
painting
.
price
FROM
artist
INNER
JOIN
painting
INNER
JOIN
(
SELECT
a_id
,
MAX
(
price
)
AS
max_price
FROM
painting
GROUP
BY
a_id
)
AS
tmp
ON
painting
.
a_id
=
artist
.
a_id
AND
painting
.
a_id
=
tmp
.
a_id
AND
painting
.
price
=
tmp
.
max_price
;
Yet another way to answer maximum-per-group questions is to use a
LEFT
JOIN
that
joins a table to itself. The following statement identifies the highest-priced painting per
artist ID (use
IS
NULL
to select all the rows from
p1
for which there is
no
row in
p2
with
a higher price):
mysql>
SELECT p1.a_id, p1.title, p1.price
->
FROM painting AS p1 LEFT JOIN painting AS p2
->
ON p1.a_id = p2.a_id AND p1.price < p2.price
->
WHERE p2.a_id IS NULL;
+------+-------------------+-------+
| a_id | title | price |
+------+-------------------+-------+
| 1 | Mona Lisa | 87 |
| 3 | The Potato Eaters | 67 |
| 4 | Les Deux Soeurs | 64 |
+------+-------------------+-------+
To display artist names rather than ID values, join the result of the
LEFT
JOIN
to the
artist
table:
mysql>
SELECT artist.name, p1.title, p1.price
->
FROM painting AS p1 LEFT JOIN painting AS p2
->
ON p1.a_id = p2.a_id AND p1.price < p2.price
->
INNER JOIN artist ON p1.a_id = artist.a_id
->
WHERE p2.a_id IS NULL;
+----------+-------------------+-------+
| name | title | price |
+----------+-------------------+-------+
| Da Vinci | Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+----------+-------------------+-------+
The self-
LEFT
JOIN
method is perhaps less intuitive than using a temporary table or a
subquery.
Which technique is better: the temporary table or the subquery in the
FROM
clause? For
small tables, there might not be much difference either way. If the temporary table or
subquery result is large, a general advantage of the temporary table is that you can index
it after creating it and before using it in a join. However, as of MySQL 5.6, the optimizer
automatically adds an index to subquery results in the
FROM
clause if it estimates that
