Select * from Contact1
Where Company <> 'Z_CANDIDATE'
  And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
  AND NOT EXISTS (Select Client_Accountno from RSM_KPI Where RSM_KPI.Client_Accountno = Contact1.Accountno)

--Creating the tables
create table Demo (I INT NULL)
go
create table Demo2 (I INT NULL)
go

--Inserting the data
insert into Demo (I) 
select 1 union select 2 
go

insert into Demo2 (I) 
select 1 union select 3 
go

--At this point Demo2 does not 
--contain the value NULL, so this should work
select * from Demo
where I not in (select I from Demo2)

select * from Demo
where not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)

--After inserting the value Null the first query
--won't return any records
insert Demo2 (I) values (NULL)

select * from Demo
where I not in (select I from Demo2)

--I need to exclude the value Null from the subquery
select * from Demo
where not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)

--cleanup
drop table Demo
drop table Demo2

WHERE Client_Accountno is not null

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
  And Accountno not in (Select Distinct Client_Accountno from RSM_KPI WHERE Client_Accountno is not null)

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
  And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)