sql server - Compare the results of two queries and return missing rows

Question

I have two queries, shown here:

Query #1

select cm 
from cm3
where inactive = 0

Query #2

select cm 
from cm3
join bo on cm3.cm = bo.seller
where status = 'installed'
  and installdate between '20201201' and '20201231'
group by cm
having count(*) > 0

Query #1 returns every seller that is active, while query #2 returns every seller with at least 1 contract installed.

I want to find out which sellers don't have any installed contracts.

I've tried to have query two as a subquery on the WHERE clause and also having both queries separated by an EXCEPT, like shown here.

Sub-query on the WHERE clause

select cm 
from cm3
where cm not in (select cm 
                 from cm3
                 join bo on cm3.cm = bo.seller
                 where status = 'installed'
                   and installdate between '20201201' and '20201231'
                 group by cm
                 having count(*) > 0)

EXCEPT

select * 
from 
    (select cm from cm3 where inactive = 0) as query1
except
select * 
from 
    (select cm 
     from cm3
     join bo on cm3.cm = bo.seller
     where status = 'installed'
       and installdate between '20201201' and '20201231'
     group by cm
     having count(*) > 0) as query2

question from:https://stackoverflow.com/questions/65848855/compare-the-results-of-two-queries-and-return-missing-rows

Answer 1

To solve this I used EXCEPT, but removed the subqueries:

select cm from cm3 where inactive = 0
except
select cm from cm3
     join bo on cm3.cm = bo.seller
  where status = 'installed'
     and installdate between '20201201' and '20201231'
group by cm
  having COUNT(*) > 0