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.

possible duplicate of [MINUS operator in MySQL?](http://stackoverflow.com/questions/8386280/minus-operator-in-mysql) — Air, Jan 15 '15 at 21:55
@Jayvee: won't help. MySQL doesn't support `MINUS` (or the standard equivalent operator `except`) — , Jan 15 '15 at 22:00
@a_horse_with_no_name, of course, sorry!, I was thinking oracle for some reason — Jayvee, Jan 15 '15 at 22:10

Alisa · Accepted Answer · 2015-01-15T22:17:26.097

13

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)

edited Jan 15 '15 at 22:17

answered Jan 15 '15 at 21:57

Alisa

2,892
3
31
44

2

You don't need the inner `DISTINCT`. Slows down the query for no gain. – Tripp Kinetics Jan 15 '15 at 21:57
2

Plus: `distinct` is ***NOT*** a function – Jan 15 '15 at 22:01
Thanx @TrippKinetics and a_horse_with_no_name. Fixed. – Alisa Jan 15 '15 at 22:01
The first query works. However, the second query would not work. What that would give me is all the territories that have at least one NULL value. What I want is all the territories that do not have any NON-NULL values. The second query gives me 129 results instead of 13. – David542 Jan 15 '15 at 22:05
@David542, please check the third solution. – Alisa Jan 15 '15 at 22:20

David542 · Answer 2 · 2015-01-15T22:07:04.890

1

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)

edited Jan 15 '15 at 22:07

answered Jan 15 '15 at 21:55

David542

104,438
178
489
842

I don't think this is the best way to answer this question, but it is a reasonable answer. I don't think it should be downvoted. – Gordon Linoff Jan 15 '15 at 22:43

SQL Set Difference

2 Answers2