Hello! There is such a database:

alt text

And there is such a task:

Display the following fields "Buyer Name" (Buyers.Name), "Order Number" (Orders.Id), "Book Name" (Books.Name) only for those orders in which the number of books is less than three

I solved the problem like this:

SELECT b.Name, O.OrderId, bk.Name FROM Orders O JOIN Buyers b ON b.Id = O.BuyerId JOIN BooksInOrder bo ON bo.OrderId = O.OrderId JOIN Books bk ON bk.Id = bo.BookId WHERE O.OrderId IN ( SELECT OrderId FROM BooksInOrder GROUP BY ORDERID HAVING COUNT(*) < 3 )

But I would like to know whether it is possible to write a query more optimally (primarily in terms of performance)? Thank you in advance.

  • Plan must be watched. And so I see, you have double scan plates BooksInOrder. If the version of SQL SERVER> 2000, you can do without grouping using WINDOW FUNCTION. -

1 answer 1

I would advise to rewrite IN to JOIN