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

Question

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 27-Dec-21 12:47pm

ahmed_sa

Updated 29-Dec-21 7:02am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-12-28T23:19:00

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

Maciej Los · Answer 2 · 2021-12-29T07:02:00

Solution 2

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.

Posted 29-Dec-21 7:02am

Maciej Los