SQL joins are very common to fetch the data from multiple tables. One of the other big advantage of joins is that they can be used to update the data in bulk in the database. This is quite helpful to avoid the use of the Cursors or the While loops which can affect the performance badly if the records are in thousands.
Lets take a simple example where you have data of users in a Temporary table. Let’s insert the data into this table :
As above, we are having 4 records which are not in Active state.
Here, you can have the data from any external data source into the Temporary table. I have got the data from the database and will be updating the same in the database using the join with this temporary table for this example. Finally we run the Update query using the JOIN as we can see below :
As we can see, the JOIN query updates the records to StateId = 2 only for the records which we are having matching records in the Temporary table.
I haven’t tried testing the time for the execution of this with large number of records and analysis using the SQL profiler or any other tool but i feel this is certainly a better approach. Similarly it can be used to Delete the records from the table.