Performance of JOIN vs SELECT...WHERE with an example

Question

I found this example on the MySQL Tutorial:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

My question: is the subquery (SELECT MAX(price) FROM shop) done once a time, or it is done repeatedly until the max price for the query is found?

In terms of performance is this other solution better?

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

Thanks.

Answer 1

The sub query is non correlated so any sensible implementation will only evaluate it once. Note that MySQL does have a problem with IN though where the semantically equivalent

SELECT article, dealer, price
FROM   shop
WHERE  price IN (SELECT MAX(price) FROM shop);

Leads to the sub query being evaluated multiple times.

As far as evaluating performance you would need to look at the explain plan for both in your particular RDBMS.

The most efficient solution might be to use SELECT TOP .. WITH TIES or equivalent if you have a covering index on the price column and your RDBMS has such a construct.

Answer 2

You've got tags for MySql, T-Sql and PL/Sql, I suspect the answer is different for each.

The answer could also depend on what indexes you have and how unique the values in the [price] field are.

Run the query analyser to see what the actual query-plan is.

Answer 3

to answer your question, the scalar subquery (SELECT MAX(price) FROM shop) is run once and then passed to the main query as a value, in MySQL.

So that query is as quick as anything else you could come up with.

Answer 4

I used SQL Server 2008 to these three variations of the aquery. In my testing I queries the AdventureWorks DB using ProductInventory in the Production schema. The three queries are:

declare @max int
Select @max =  MAX(Quantity) FROM [AdventureWorks].[Production].[ProductInventory]
SELECT TOP 1000 [ProductID]
      ,[LocationID]
      ,[Shelf]
      ,[Bin]
      ,[Quantity]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Production].[ProductInventory]
  WHERE Quantity = @max

SELECT TOP 1000 [ProductID]
      ,[LocationID]
      ,[Shelf]
      ,[Bin]
      ,[Quantity]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Production].[ProductInventory]
  WHERE Quantity = (Select MAX(Quantity) FROM [AdventureWorks].[Production].[ProductInventory])


SELECT TOP 1000 AW1.[ProductID]
      ,AW1.[LocationID]
      ,AW1.[Shelf]
      ,AW1.[Bin]
      ,AW1.[Quantity]
      ,AW1.[rowguid]
      ,AW1.[ModifiedDate]
  FROM [AdventureWorks].[Production].[ProductInventory] AW1
LEFT JOIN [AdventureWorks].[Production].[ProductInventory] AW2 ON AW1.Quantity < AW2.Quantity
WHERE AW2.ProductID IS NULL; 

Using the "Show estimated query plan" icon I can compare the exection events for three cases. The results are:

1. Declaring a variable and filling the variable is 5% faster than a sub-select in the where clause.
2. The join is 98% slower than the subselect
3. The join is 99% slower than the variable

My suggestion is to declare a variable and fill it. Use the variable in the WHERE clause

Answer 5

I can beat both:

SELECT article, dealer, price
FROM   shop
WHERE  price=MAX(price)

Edit: Whoops, not working on my test server :/

Answer 6

Using a join should be better than a nested sub-query.

Answer 7

You should not care. Any decently modern database server will understand what you want and perform the query in the most efficient way it can. SQL is declarative, not imperative (ie. you say what results you want, you don't say how it is to be retrieved).