Not in not working

1

HI This is just a basic, but both not in, and in do not return anything. The second table also has people-id, but do I make the select a or b?
I want to see the rows that are NOT in Referrals_made_to_programs_view
Select b.full_name from
all_people_expanded_view b
where b.people_id not in (Select b.people_id from Referrals_made_to_programs_view a)

2

b. in the subquery might be the issue?

1 Like
3

You need to make sure there is no NULL value in the NOT IN, because it will cause the NOT IN to fail:
SELECT ... not in (Select b.people_id from Referrals_made_to_programs_view a Where b.people_id IS NOT NULL)

1 Like
4

Usually I prefer to use the NOT EXISTS clause like shown below

SELECT b.full_name
FROM all_people_expanded_view b
WHERE NOT EXISTS
(
	SELECT *
	FROM Referrals_made_to_programs_view a
	WHERE a.people_id = b.people_id
);
1 Like
5

Oops, I did not read closely enough. The subquery should use the alias from its own table:

SELECT ... where b.people_id not in (Select a.people_id from Referrals_made_to_programs_view a Where a.people_id IS NOT NULL)