How to use inner join instead of left join on query below in case of data not exist on chemical master ?

Question

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

 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 

so How to do that Please ?

Answer 1

Hi @ahmed salah ,

Cannot use INNER JOIN instead of LEFT JOIN, try to use OUTER APPLY instead of LEFT JOIN:

      update r   
      set r.HasChemical=case when t.partid is not null   
                        then 'HasChemical' else 'No' end  
      from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)  
      outer apply(select *   
                  from Parts.ChemicalMaster cm   
                  where cm.partid=r.partid) t  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi,

How to use inner join instead of left join

Well... how can you use - instead of + in order to get the sum of X+Y ?

It's about the same analogy

By the way, the answer is: X-(Y * (-1)), but is this makes sense?

In this action we are calculating multiple operations (multiplication and subtraction ) instead of one. Moreover, a multiplication operation costs a lot more than a addition operation! behind the scenes multiplication is calculated as multiple times of an addition action.

i need to use inner join instead of left join because data is very big on table

This makes no sense. First of all you should let SQL Server find a good execution plan by itself. SQL Server is a very advance and complicated application and a lot of effort in the development was on adding advance algorithms and to make the server choose the right one according to the specific database and the statistics of the tables, which mean, that SQL Server should be able in most cases to choose the best solution according to the table size between other parameters

Moreover, INNER JOIN does not include all the information which LEFT JOIN returns, so you will need to bring the missing information probably and this mean more actions which most likely will cost more resources and perform worse.

With that said, in order to help you with best solution you should provide (at least) your Live Execution Plan (full XML and not just screenshot) and the exact structure of the relevant entities - queries to create the table and any existing index or constrain or foreign key and queries to insert some ample data

Answer 3

Hi @ahmed salah
I recommend you split this sql query, if you don’t want to take too much time.
First you can update all the HasChemical set to “No” ，then set HasChemical =‘ HasChemical’ if partid is in table Parts.ChemicalMaster .

Here is a demo，you can try it.

create table largetable  
(HasChemical VARCHAR(20),  
 partid int)  
 insert into largetable  
 select '',1  
 union all  
  select '',2  
   union all  
  select '',3  
   union all  
  select '',4  
   union all  
  select '',5  
     union all  
  select '',6  
   union all  
  select '',7  
   union all  
  select '',2  
  
  create table smalltable  
  (partid int)  
  
  insert into smalltable  
  select 1  
  union select 2  
  
  --STEP ONE  
   update L SET L.HasChemical ='No' from largetable L   
     --STEP TWO  
  update L SET L.HasChemical ='HasChemical' from largetable l WHERE L.partid IN (SELECT partid FROM smalltable)   
  
  SELECT * FROM largetable  

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

How about you try this? At least it's easier to read and should be efficient

update r
set r.HasChemical=
CASE WHEN EXISTS
    (
        SELECT *
        FROM Parts.ChemicalMaster cm
        WHERE cm.partid=r.partid 
    )
    THEN 'HasChemical'
    ELSE 'No'
END
from ExtractReports.dbo.FinalComplanceDataDelivery r