How to use inner join instead of where exists statment ?

Question

Post reply

How to use inner join instead of where exists statment ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

September 13, 2021 at 10:43 am

#3929059

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

As its a existence check, you should just be able to remove the where exists and switch it to an inner join instead, you have the syntax and the joining columns already so you just change

where     
   exists(select 1 from

to an inner join

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

Just be careful switching to an inner join if that is a 1 to many 🙂

pietlinden SSC Guru Points: 63503 More actions · Answer 3

There's one big thing you are missing from your question - What are you trying to accomplish? Changing the EXISTS clause to a join will cause you to have duplicate values from the parent table. If you want that, that's fine. But you haven't explicitly stated what your goal is in terms of query results. That's why you're getting generic answers.

This reply was modified 3 years, 2 months ago by pietlinden.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

pietlinden wrote:

There's one big thing you are missing from your question - What are you trying to accomplish? Changing the EXISTS clause to a join will cause you to have duplicate values from the parent table. If you want that, that's fine. But you haven't explicitly stated what your goal is in terms of query results. That's why you're getting generic answers.

It's an extremely valid point.

Why they (or anybody else) would want to do that?

I always user any opportunity to do the opposite thing - replace an INNER JOIN with WHERE EXISTS.

Unless i's a test question meant to check some basic SQL skills, the answer is - don't do it.

And if it is a test question - the answer should be - RTFM.

_____________
Code for TallyGenerator