How to replace where exists with inner join?

Transact-SQL
,
1

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

2

Just put another inner join and remove where clause:

Inner join extractreports.dbo.getrelatedkeyandvaluepackage g ON g.Featureid=dd.acceptedvalueid and g.valueid=pa.value

But what's the issue you are facing and why do you want to convert this to an inner join?

3

Be aware that if you do replace the WHERE EXISTS with an INNER JOIN, it is possible that you may not get the same result set. For example, if there is more than one matching row in the getrelatedkeyandvaluepackage table for a given combination of dd.acceptedvalueid and pa.value.

4

What have you tried

5

this is good question because when i use inner join query run
and give me 500000 rows on one minute
using inner join
and when using where exist as above
it take 3 hours
so what is issue that make big different on time between two queries

6

I don't know why the WHERE EXISTS would take significantly longer, except to guess that there probably are a large number of rows that do not meet the criterion, and searching for those, and coming out with a negative result, can be resource intensive. But I am just speculating. You should look through both query plans, which would tell you the story.

I had missed that you are grouping by partid, so multiple matches should not affect your result set if you use inner join.

As an aside, using the NOLOCK hint is discouraged; you can search for analysis on why that is so.

7

I would have thought exists would be faster than an inner join.
When you run the query in SSMS and also select execution plan or estimate execution plan, are there any index recommendations?
I would actually start writing the query using only exists:

SELECT pr.partid
  from parts.Nop_Part pr with(nolock) 
 where exists(select *
                from extractreports.dbo.RequiredPartsPL rp with(nolock) 
               where rp.partid=pr.partid
                 and exists(select *
                              from Parts.Nop_PackageAttribute pa WITH(NOLOCK) 
                             where pa.packageid=pr.packageid 
                               and exists(select *
                                            from dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) 
                                           where pa.[Key] = dd.ColumnNumber 
                                             and exists(select *
                                                          from extractreports.dbo.getrelatedkeyandvaluepackage g 
                                                         where g.Featureid=dd.acceptedvalueid 
                                                           and g.valueid=pa.value)
                                          )
                           )
             )