SQL - Max Vs Inner Join

Question

I have a question on which is a better method in terms of speed. I have a database with 2 tables that looks like this:

Table2

UniqueID Price

1                100
2                200
3                300
4                400
5                500

Table1

UniqueID User
1                Tom
2                Tom
3                Jerry
4                Jerry
5                Jerry

I would like to get the max price for each user, and I am now faced with 2 choices:

Use Max or using Inner Join suggested in the following post:Getting max value from rows and joining to another table

Which method is more efficient?

Which dbms are you using? Different products optimize things differently! — jarlh, Jun 11 '15 at 10:58
Hi, thanks all for your reply. The link between the 2 table is UniqueID. And I am using MS Access — user2659149, Jun 11 '15 at 11:21

score 1 · Accepted Answer · answered Jun 11 '15 at 10:50

The answer to your question is to try both methods, and see which performs faster on your data in your environment. Unless you have a large amount of data, the difference is probably not important.

In this case, the traditional method of group by is probably better:

select u.user, max(p.price)
from table1 u join
     table2 p
     on u.uniqueid = p.uniqueid
group by u.user;

For such a query, you want an index on table2(uniqueid, price), and perhaps on table1(uniqueid, user) as well. This depends on the database engine.

Instead of a join, I would suggest not exists:

select u.user, p.price
from table1 u join
     table2 p
     on u.uniqueid = p.uniqueid
where not exists (select 1
                  from table1 u2 join
                       table2 p2
                       on u2.uniqueid = p2.uniqueid
                  where p2.price > p.price
                 );

Do note that these do not do exactly the same things. The first will return one row per user, no matter what. This version can return multiple rows, if there are multiple rows with the same price. On the other hand, it can return other columns from the rows with the maximum price, which is convenient.

Because your data structure requires a join in the subquery, I think you should stick with the group by approach.

SQL - Max Vs Inner Join

1 Answers1