Search code examples
sqlt-sqlsubqueryleft-join

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".

enter image description here

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';

Solution

  • 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.