Avatar of patd1
patd1
Flag for United States of America asked on

converting left join with a null filter to where not exists

I have a query that brings records from table A that do not exist in table B or table C.
It has a left join with a filter where value is null for both tables B and C.

I want to see if it works better if I change it to use where Not exists clause, but my not exist statement doesn't not seem right because I get 24K rows with the original query, but none with my new query. Please review and suggest corrections.
Than you in advance.
--original sql returns 24000 rows
Select cl.*
	FROM TableA cl WITH (NOLOCK)
		JOIN TableD sm  WITH (NOLOCK)
			on cl.etid = sm.etid
			and content_type = 'BIL'
			and active_flag = 'Y'
		LEFT OUTER JOIN TableB b
			on b.id = cl.id
		LEFT OUTER JOIN TableC rm  WITH (NOLOCK)
			on rm.bill_id = cl.id
		where b.id is null
		and rm.bill_id is null

Open in new window


--new sql returns 0 records
Select cl.*
	FROM TableA cl WITH (NOLOCK)
		JOIN TableD sm  WITH (NOLOCK)
			on cl.etid = sm.etid
			and content_type = 'BIL'
			and active_flag = 'Y'
	 where not exists (Select id from TableB) 
	     and not exists (Select bill_id from TableC)	

Open in new window

Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

No problem.  It happens all the time.
patd1

ASKER
Thank you all. The query now worked and it took only 15 sec as the original query took 56 sec. Way to go! Thanks again.
Anthony Perkins

You could probably optimize it further if you are interested.
Your help has saved me hundreds of hours of internet surfing.
fblack61