CODEWITHSUNDEEP
sql
pascal b
pascal b

Reputation: 371

Which of these two queries is more efficient or run best?

query 1

SELECT m.Name, p.Name, p.Price
    FROM Manufacturers m INNER JOIN Products p
        ON m.Code = p.Manufacturer
WHERE p.Price IN (SELECT MAX(pp.Price) FROM Products pp GROUP BY pp.Manufacturer);

query 2

  select m.Name as ManufName, p.Name as ProductName, p.Price as ProductPrice
    from Manufacturers m inner join Products p
      on m.Code = p.Manufacturer
        where p.Price =   (select max(sub_p.Price)
                                  from Products sub_p
                                    where m.Code = sub_p.Manufacturer);

Which one should take less time?

Upvotes: 0

Views: 116

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2656

It depends. Size of the tables, indices, etc. Try them both and compare.

However, knowing nothing of your database, I will wager a guess that query 1 should perform better. Query 2 has a correlated sub query and that will cause the optimizer to use a nested loop.

Upvotes: 1

Related Questions