CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60841

using an inner or left join in an update?

Though my sql knowledge is pretty good, I cannot get my head around the difference in a left vs inner join specifically when doing an update.

employee_table
column1:id
column2:socialsecurity


private_info_table
column1:id
column2:socialsecurity

I need to update employee_table.socialsecurity to be private_info_table.socialsecurity

should I do a left or inner join: ???

update e
set e.socialsecurity=p.socialsecurity
from employee_table e
join private_info_table p  --should this be left or inner join?
on p.id=e.id

Upvotes: 1

Views: 2223

Answers (2)

mitch
mitch

Reputation: 1831

Left Join

Select *
from A 
left join B on a.id = b.pid

In the conditions a.id = b.pid, every row is returned for both A and B BUT if the value from A (i.e. a.id) doesn't match the value from B (i.e., B), all fields of B will be null. On the other hand, for those rows where this condition is true, all values for B are shown. Notice that A's values have to be returned because it is on the left of the 'left' keyword.

Inner Join

Select *
from A 
inner join B on a.id = b.pid

Rows are returned for rows where a.id = b.pid is true, otherwise no rows are returned if it is false. This is a mutually exclusive join.

In your case, don't use a left join because all records on the left of the 'left' keyword will be updated with null or non-null values. This means you will unintentionally update non-matched record with null values based upon my description of left joins.

Upvotes: 4

Avitus
Avitus

Reputation: 15968

It should be an inner join if you just want to update those records that are in both tables. If you do a left join it will update the employee table to be null wherever the id isn't found in the private info table.

The reason this matter is if you have some social security numbers in the employee table that already that aren't found in the private info table. You wouldn't want to erase them with a null value.

Upvotes: 2

Related Questions