How to replace where exists with inner join ?

Question

How to replace where exists with inner join ?

ahmed salah 3,216

I work on sql server 2012 i face issue ican't replace (where exists)
by inner join
so How to do it

SELECT pr.partid

        from 

                 parts.Nop_Part pr with(nolock) 
                inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid

                inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid 

                inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber --and dd.acceptedvalueid=64


                where     
  exists(select 1 from extractreports.dbo.getrelatedkeyandvaluepackage g where g.Featureid=dd.acceptedvalueid and g.valueid=pa.value  )

                group by pr.partid

so how to replace statement above by inner join instead of using where exists
statment

Tom Phillips 17,786 Reputation points

2021-09-13T11:43:03.703+00:00

Why do you feel the need to replace exists with inner join? Do you need data from getrelatedkeyandvaluepackage?

If there is more than 1 match in getrelatedkeyandvaluepackage, the inner join will cause your code to return more rows than before.
MelissaMa-msft 24,246 Reputation points Moderator

2021-09-15T08:20:17.503+00:00

Hi @ahmed salah ,

Could you please validate all the answers so far and provide any update?

Best regards,
Melissa

2 answers

Your answer

Tom Phillips 17,786 Reputation points

2021-09-13T11:43:03.703+00:00

Why do you feel the need to replace exists with inner join? Do you need data from getrelatedkeyandvaluepackage?

If there is more than 1 match in getrelatedkeyandvaluepackage, the inner join will cause your code to return more rows than before.
MelissaMa-msft 24,246 Reputation points Moderator

2021-09-15T08:20:17.503+00:00

Hi @ahmed salah ,

Could you please validate all the answers so far and provide any update?

Best regards,
Melissa

Answer 1

Hi @ahmed salah ,

What is the purpose for replacing exists with inner join？

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and the expected result of the sample after executing your query.

Actually EXISTS and INNER JOIN have their own uses. If you only would like to return some records from a set, use EXISTS. If you also would like to return the columns in the another table, use INNER JOIN. You only have to notice the situation of one-many.

I made a test from my side by creating your tables and inserting some sample data and it was working like below:

SELECT distinct pr.partid    
from   parts.Nop_Part pr with(nolock)   
inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid                      
inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid   
inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber  
inner join extractreports.dbo.getrelatedkeyandvaluepackage g on g.Featureid=dd.acceptedvalueid and g.valueid=pa.value

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
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 2

Viorel 126.9K

Try something like this:

inner join (select top(1) Featureid, valueid from extractreports.dbo.getrelatedkeyandvaluepackage) g on g.Featureid = dd.acceptedvalueid and g.valueid = pa.value

Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2021-09-13T21:44:30.547+00:00

That does not seem like good advice. This will return one row of there are multiple matches, but which row is undefined. Writing such queries are odd, to say the least.

Ahmed needs to clarify what he is looking for.

Share via

How to replace where exists with inner join ?

2 answers

Your answer