Link to home
Start Free TrialLog in
Avatar of patd1
patd1Flag 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

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No problem.  It happens all the time.
Avatar of 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.
You could probably optimize it further if you are interested.