SQL Join that finds non matches

I have two tables “SellerDetails” and “ANL” and both have key fields of “Seller Ref No” and “Sales Year”.

This giving me where there is a match on Seller Ref No where the Sales Year is “20” and the Department is “Remote Sales”

SELECT si.[Seller Ref No] FROM dbo.SellerDetails AS si, dbo.ANL AS a
WHERE si.[Seller Ref No] = a.[Seller Ref No]
AND si.Department = 'Remote Sales'
AND si.[Sales Year] = '20';

But how do I find out the records that are in “SellerDetails” but NOT in “ANL” where the Sales Year is “20” and the Department is “Remote Sales”? I have tried this but it returns a quantity much larger than I expect. I have also tried changing ‘=’ to ‘<>’ but that provided a worse result

SELECT si.[Seller Ref No]
FROM dbo.SellerDetails AS si
LEFT JOIN dbo.ANL AS a ON (si.[Seller Ref No] = a.[Seller Ref No])
WHERE si.Department = 'Remote sales'
AND si.[Sales Year] = '20';

Answer

You can use not exists:

select s.*
from dbo.SellerDetails s
where 
    s.Department = 'Remote Sales'
    and s.[Sales Year] = 20
    and not exists (select 1 from dbo.ANL a where a.[Seller Ref No] = s.[Seller Ref No])

It is unclear whether you want to use [Sales Year] as a correlation condition too: it is present in both tables, but your query does not use that relation. If you want it, then:

select s.*
from dbo.SellerDetails s
where 
    s.Department = 'Remote Sales'
    and s.[Sales Year] = 20
    and not exists (select 1 from dbo.ANL a where a.[Seller Ref No] = s.[Seller Ref No] and a.[Sales Year] = s.[Sales Year])

Side note: here is the query you presumably wanted to write, using a left join:

SELECT si.[Seller Ref No]
FROM dbo.SellerDetails AS si
LEFT JOIN dbo.ANL AS a ON si.[Seller Ref No] = a.[Seller Ref No]
WHERE 
    si.Department = 'Remote sales'
    AND si.[Sales Year] = '20'
    AND a.[Seller Ref No] IS NULL

The last condition in the WHERE clause filters out matching rows.