In this example, I'm trying to return a list of values (in this case, company names) that have no entries in another table (entries in this case meaning invoices). In other words, I'm trying to return a list of companies that have no invoices. Here is my code:
From tblCompany join tblInvoice
ON tblCompany.CompanyID = tblInvoice.CompanyID
Where tblCompany.CompanyID NOT IN
Use left join and filter on nulls:
select CompanyName from tblCompany left join tblInvoice on tblCompany.CompanyID = tblInvoice.CompanyID where tblInvoice.CompanyID is null
This works because missed joins return nulls in the joined table's values.