Click here to Skip to main content
15,309,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I work on sql server 2014 I need to use inner join instead of left join
and update Haschemical i case of exist by haschemical or No in case of not exist
on statement below

i need to use inner join instead of left join because data is very big on table
ExtractReports.dbo.FinalComplanceDataDelivery may be 20 million so query take too much time to execute

so How to do that Please ?

What I have tried:

update r set r.HasChemical=case when cm.partid is not null then 'HasChemical' else 'No' end
     from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)
     left join 
     Parts.ChemicalMaster cm with(nolock) on cm.partid=r.partid 
Posted
Updated 29-Dec-21 7:02am
v2

You don't change the join just to shorten the execution time of the query!

Using an inner join in this case will mean that cm.partid will never be null and changing the query in that way completely changes its behaviour.

You should instead look to other ways of achieving the goal

- Ensure that tables ExtractReports.dbo.FinalComplanceDataDelivery and Parts.ChemicalMaster both have indexes on their respective partid columns

- Instead of using
SQL
set r.HasChemical=case when cm.partid is not null then 'HasChemical' else 'No' end
try the slightly more efficient (and more readable)
SQL
set r.HasChemical=IsNull(cm.partid, 'No')

- Instead of using strings 'HasChemical' (shouldn't that be 'Yes' anyway??) and 'No' consider using a bit column type with 0 = False/No and 1 = True/Yes

- If column r.HasChemical is set up with a default (really easy if you use a bit column type) implying 'No'/False/0 then you can change the join to an Inner join and only update the column where HasChemical = 'HasChemical'/True/1 e.g.
SQL
update r set r.HasChemical='HasChemical'
     from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)
     inner join Parts.ChemicalMaster cm with(nolock) on cm.partid=r.partid
   
v2
Comments
Maciej Los 29-Dec-21 13:01pm
   
5ed!
In addition to @CHill60 answer, i'd suggest to read this: Visual Representation of SQL Joins[^]
This will help you uderstand the difference between types of joins.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900