Left join (where table on the right.joined column is null) to exclude records (1 Viewer)

[Isaac]

I have a really dumb question that makes it appear I've never written a query before, but here goes. Just to make sure I haven't gotten amnesia or my head is screwed on wrong today.

If I have one table.....MAINTABLE, and one "exclusion" query, let's call it EXCLUSION. I want to select records from MAINTABLE but make sure they are NOT in EXCLUSION. (EXCLUSION exists for the purpose of listings things I want to make sure NOT to include).

Isn't this 100% effective? Any gotcha's or something I've forgotten?

I'm not worrying about Access sql syntax here - just putting this here for general ansi syntax, although I am doing this in Access......and dragging two ID fields for a left join, where maintable is on the left, and exclusion is on the right.

select MAINTABLE.* FROM MAINTABLE LEFT JOIN EXCLUSION on MAINTABLE.ID=EXCLUSION.ID where EXCLUSION.ID is null

this is my standard way of excluding things pretty much always rather than using not exists or a subquery etc

 

[plog]

Yes, that will do it.

LEFT JOIN makes everything from MAINTABLE show in the query
ON will not connect NULL to NULL so only EXCLUSION records with data in ID will make the connection
WHERE makes sure that if a connection is made they don't show up

 

[isladogs]

You can also use the unmatched query wizard to achieve exactly the same result

There is absolutely no need to use NOT EXISTS or a subquery for this purpose

 

[Isaac]

Agree! ok just making sure i wasn't remembering wrong.