troubleshooting Question

converting left join with a null filter to where not exists

Avatar of patd1
patd1Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
6 Comments3 Solutions528 ViewsLast Modified:
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

--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)	
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros