0

I need to know which of two queries are more efficient.

First one I am using a join statement:

"Update  a 
 set a.TemporaryStock = isnull(a.TemporaryStock,0) - 1 
 from InventoryMaster a 
 join InventoryTransTemp b on a.InventoryItemID = b.InventoryItemID 
 where b.InventoryTransTempID='" + aInTransID + "';";

Second query I am using select inside where statement:

Update InventoryMaster 
set TemporaryStock = isnull(TemporaryStock,0) - 1 
where InventoryItemID = (Select top (1) InventoryItemID 
                         from InventoryTransTemp 
                         where InventoryTransTempID='" + aInTransID + "');

Which of the two queries is clearer for SQL Server, and why?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dim
  • 433
  • 1
  • 9
  • 23
  • This has already been answered : https://stackoverflow.com/questions/2577174/join-vs-sub-query – KenL Jan 26 '19 at 16:23
  • @KenL Please do not compare MySQL to MS SQL Server(they are two different products). Dim You should always compare actual execution plans and statistics. I would use first approach(FROM JOIN). Plus it looks like you are concatenating SQL String which could lead to SQL Injection. I recommend to switch to parametrized query. And the last thing SQL Server 2008 has support only to [July 2019](https://www.microsoft.com/en-us/sql-server/sql-server-2008). – Lukasz Szozda Jan 26 '19 at 16:29
  • So you recommend join? – Dim Jan 26 '19 at 16:32
  • In general, joins in SQL Server faster than sub queries, because they open possibilities to use HASH or MERGE joins next to nested loops, while subqueries limited to nested loop and sub query to be executed once per row – Alexander Volok Jan 26 '19 at 16:33
  • Join is preferable, but plan might be absolutely the same. So, just compare both query plans and you'll get an answer. – Slava Murygin Jan 26 '19 at 16:54

0 Answers0