Select All Records From Table That Do Not Exist In Secondary Table

Question

This should be straight forward, but I am getting my tail kicked with the proper syntax.

I have this syntax

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM 
    ___SampleRequired bm
LEFT JOIN 
    ___Sample td 
    ON LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name))
WHERE 
    bm.Type LIKE '%Mandate%'
    AND bm.Location = 'StapleChase'

which I want to show all beer that exist in the SampleRequired Table that DO NOT exist in the Sample Table. When I execute the above query I get 24 results returned. My expected returned result set is 23 because

BLUE MOON BELGIAN WHITE

exists in both of the tables.

How should I change this query so that I ONLY get the results that exist in SampleRequired that DO NOT exist in Sample?

***Had to use pastebin bc SO complained my post was mostly code...sorry

https://pastebin.com/jH6eX2du

S3S · Accepted Answer · 2019-10-07T13:50:25.500

Using NOT EXISTS

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM 
    ___SampleRequired bm
WHERE NOT EXISTS 
   (SELECT 1 
    FROM ___Sample td 
    WHERE LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name)))
AND bm.Type LIKE '%Mandate%'
AND bm.Location = 'StapleChase'

Also, Max Vernon has a nice answer comparing LEFT JOIN vs NOT EXIST It's also more common to see this in the wild versus the LEFT JOIN method where you also need to add table.key IS NULL in the WHERE clause. i.e td.Beer_name IS NULL

And, Martin Smith comparing NOT IN vs NOT EXIST.

score 1 · Answer 2 · answered Oct 07 '19 at 13:55

Although I think scsimon answer is da best, you can get the same result by adding:

AND td.Beer_name IS NULL

LEFT JOIN selects all rows from __SampleRequired table that match WHERE condition, but add nulls for those __Sample rows that do not match ON condition.

Select All Records From Table That Do Not Exist In Secondary Table

2 Answers2