Sales without associated accounts with a Left Join?

Question

I thought I understood how to do this, but I might be missing something. Would this not work?

SELECT COUNT(*)
FROM Sale A
LEFT JOIN Account B on A.Sale_id= B.Sale_id
WHERE account_id IS NULL

Answer 1

You can do this in one of the many ways. For Instance

Use NOT IN Operator

Select 
     * 
from Sale 
where 
    Sale_id NOT IN (SELECT DISTINCT(Sale_id) FROM Account)

Subquery will return the sale ids which has records in Account Table. NOT IN operator returns all other records from Sale Table

Use LEFT JOIN

Select 
   s.*
from Sale s left join Account a on s.Sale_id=a.Sale_id
where a.Sale_id is null