SQL NOT IN clause not working

Asked Aug 01 '14 at 07:25

Active Aug 01 '14 at 07:29

Viewed 86 times

Im trying to get a list of names that do not exist in another list

    SELECT [Paid To]
    FROM tbl1
    WHERE [Paid To] not  in
    (select [Name ] 
    FROM tbl2
    )

I dont get any results but when I run a left join query

select top 10 *
from [tbl1] a
left join [tbl2] b
on a.[Paid To] = b.[Name ]

I get the NULLs where the 2 tables dont match. Can anyone help as to why the first query is not working?

using SQL Server 2012

edited Aug 01 '14 at 07:29

marc_s

asked Aug 01 '14 at 07:25

user2343837

Are you trying to compare name with id ??? i think that is the reason – smn_onrocks Aug 01 '14 at 07:27
1

Do you have any rows in `tbl2` where `[Name ]` is `NULL`? – Damien_The_Unbeliever Aug 01 '14 at 07:27
@smn_onrocks = no, they are both names – user2343837 Aug 01 '14 at 07:28
@Damien_The_Unbeliever - YES, why does that matter? – user2343837 Aug 01 '14 at 07:29
1

Because of three-valued logic. See the question I've linked to. – Damien_The_Unbeliever Aug 01 '14 at 07:31

0 Answers0