Link to home
Start Free TrialLog in
Avatar of patd1
patd1Flag for United States of America

asked on

sql 'not in' vs 'not exists'

I am trying to convert a sql that has a sub query with 'not in' clause to a sql with 'not exist' for performance reasons, but i get different results in both my queries. Please help me understand and get the right query that would give me the same results as the 'not in'  query.

NOT IN sql :
		select 
			id, 
			'Patient',
			'Missing Name',
			null,
			'l_c_claim',
			'p_l_name',
			pat_last_name
		from wt_l_c_bill		
		where p_l_name IS NULL
			and id not in (select b_id from app_b_r_v_item where validation_category = 'Patient')

Open in new window


'NOT EXISTS'  sql
		select 
			id, 
			'Patient',
			'Missing Name',
			null,
			'l_c_claim',
			'p_l_name',
			pat_last_name
		from wt_l_c_bill b		
		where NOT EXISTS (Select 1 from app_b_r_v_item v where validation_category = 'Patient' and b.id = v.b_id)

Open in new window


Thank You
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg 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
Avatar of patd1

ASKER

thanks got it.