CODEWITHSUNDEEP
mysqlsqldatabase
Dinesh Verma
Dinesh Verma

Reputation: 666

Difference between update query with select subquery and one with inner join

Can someone please tell me the difference between the execution of below two queries.

Update SomeTable 
Set Field1= 1234
Where Field2 in (Select Field_2 from SomeOthertable where x= 1)

Vs

Update Sometable as ST
Inner join SomeOthertable as SOT
On ST.Field2 = SOT.Field_2 and SOT.X = 1
Set ST.Field1 = 1234

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

To understand the difference in the execution, you would need to look at the execution plans.

Here is some guidance though:

  • In general, JOIN is going to be faster than IN because there are more opportunities for optimization and IN removes duplicates.
  • In the case of an UPDATE, any duplicates could cause performance issues, because the same row can be updated multiple times. This won't affect the results, because the field value does not change.

Often exists has good performance characteristics:

Update SomeTable st
    Set st.Field1 = 1234
    Where exists (Select 1
                  from SomeOthertable sot
                  where sot.Field_2 = st.Field2 and sot.x = 1
                 );

In particular, this can take advantage of an index on SomeOthertable(field2, x). The JOIN can also use this index, but EXISTS guarantees that matching rows are updated only once.

Upvotes: 1

Related Questions