SQL Set Difference

Question

How would I do the following in SQL:

select distinct(territory_id) from main_territorypricing

"minus"

select distinct(territory_id) from main_territorypricing where store_url is not null

Basically, I want all territory_ids that are contained in the top line that are not contained in the second line.

Answer 1

If you want to do as you said:

select distinct territory_id 
from main_territorypricing
where territory_id not in (
    select territory_id from main_territorypricing where store_url is not null
    )

But based on the logic of what you have described, the easier solution is:

select territory_id 
    from main_territorypricing
    where store_url is null

This, is, provided that you have only these two fields (territory_id and store_url) and the rows are unique.

Otherwise, there is another tricky solution:

select territory_id 
from main_territorypricing
group by territory_id 
having (max(store_url) is null)

Answer 2

select distinct(territory_id) from main_territorypricing
where
    territory_id in
      (select territory_id from main_territorypricing)
and territory_id not in 
    (select territory_id from main_territorypricing where store_url is not null)