CODEWITHSUNDEEP
sqlsql-server
Mohamed Abdulghani
Mohamed Abdulghani

Reputation: 11

Difference between two SQL queries and When to use each one

Actually I am confused regarding two SQL Queries. Both are giving me the same result but which one is preferred and when to use each one of them? 

UPDATE Names  
SET Names.PhoneNumber = Phones.PhoneNumber 
FROM Names  INNER JOIN  Phones ON Names.id = Phones.id

UPDATE Names 
SET Names.PhoneNumber = Phones.PhoneNumber 
FROM Phones WHERE Names.ID = Phones.ID

Upvotes: 1

Views: 141

Answers (3)

Janmejay Kumar
Janmejay Kumar

Reputation: 319

There should not be any performance difference.you can use both ways.its totally depends on you.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

They are the same. In fact, they should generate the same execution plan.

Although there is no reason to choose one over the other, the JOIN form is typically used. There is a simple reason: SQL Server does not support aliases for the UPDATE table. So, this does not work:

UPDATE Names n
    SET n.PhoneNumber = p.PhoneNumber 
    FROM Phones p 
    WHERE n.id = p.id;

However, this is fine:

UPDATE n  
    SET n.PhoneNumber = p.PhoneNumber 
    FROM Names n INNER JOIN
         Phones p
         ON n.id = p.id;

Also the use of the explicit JOIN makes it possible to use LEFT JOIN.

On the downside, the implicit JOIN version is compatible with more databases -- in particular, Postgres supports this syntax.

Upvotes: 2

Rahul Chanda
Rahul Chanda

Reputation: 35

You are observing the same result because they both meant the same meaning. The difference in the both queries is performance. As we execute both query there is a performance difference and it is due to the execution plan that the planner is putting together. WHERE clause -> he planner is choosing a route that is more efficient Join -> We can probably make the join work as fast (if not faster) by adding an index on the two columns.

Upvotes: 0

Related Questions