How to replace a complex SQL MINUS query with LEFT OUTER JOIN equivalent

Question

Trying to figure how how to replace the following, with equivalent left outer join:

select distinct(a.some_value)
from table_a a, table_b b
where a.id = b.a_id 
and b.some_id = 123
and b.create_date < '2014-01-01' 
and b.create_date >= '2013-12-01'  
MINUS
select distinct(a.some_value)
from table_a a, table_b b
where a.id = b.a_id 
and b.some_id = 123 
and b.create_date < '2013-12-01'

Can not do "NOT IN", as the second query has too much data.

Does the `MINUS` actually exclude anything? It takes rows with `b.create_date < '2013-12-01' `, but your first query already takes only these with `b.create_date >= '2013-12-01'`. — MarcinJuraszek, Jan 09 '14 at 00:55
first query is for only last month, and second query is for beginning of time to a month before it. basically, I need to not return a.some_value, if it was reported before. In this example, first query is for the month December of 2013 only and second query is everything BEFORE December of 2013 — Dmitry Grinberg, Jan 09 '14 at 00:58
I think you're missing @MarcinJuraszek's point: your first query only includes last month, so there's no sense in MINUS'ing everything from the dawn of time from it. — Joe Rinehart, Jan 09 '14 at 00:59
@JoeRinehart there is. First query is for December 2013. Second query is for before December 2013. Windows of time do not overlap. Any month there could be duplicates of a.some_value. I do not want values to be returned for the month of the "report" if same values have been previously reported. — Dmitry Grinberg, Jan 09 '14 at 01:02
Nevermind - you're right. I'd look at using EXISTS. I've had a beer, and I'm not sure I can type it right offhand, but I'll post whatever comes out in a sec. — Joe Rinehart, Jan 09 '14 at 01:05

score 6 · Accepted Answer · answered Jan 09 '14 at 01:10

6

SELECT * FROM
(
  select distinct(a.some_value)
  from table_a a, table_b b
  where a.id = b.a_id 
  and b.some_id = 123
  and b.create_date < '2014-01-01' 
  and b.create_date >= '2013-12-01'  
) x
LEFT JOIN 
(
  select distinct(a.some_value)
  from table_a a, table_b b
  where a.id = b.a_id 
  and b.some_id = 123 
  and b.create_date < '2013-12-01'
) y
ON 
  x.some_value = y.some_value
WHERE 
  y.some_value IS NULL

answered Jan 09 '14 at 01:10

Xiangpeng Zhao

268
2
8

Above query works but I got two columns with the name "some_value" and the second column contains NULL in all rows. Any idea why? – Sumit Sood Jan 16 '20 at 08:00
Both sides of the join produce columns for each dataset, even if a dataset is excluded and ultimately returns non-rows that become NULL values b/c of the left-join. Both `x` and `y` have their own `distinct(a.some_value)`, which default to being named `some_value` in both cases. The query could be modified to compensate by naming those columns: `distinct(a.some_value) AS sv_x` and `distinct(a.some_value) AS sv_y`. Then filter using `ON x.sv_x = y.sv_y WHERE y.sv_y IS NULL`. The outermost SELECT would be `sv_x AS some_value` instead of `*`. (Advice prob. late, but provided for audience's sake.) – chadjoan Jan 24 '21 at 19:31

score 0 · Answer 2 · answered Jan 09 '14 at 01:11

Here's what my brain puts out after a beer:

select distinct
    a.some_value
from
    table_a a
    join table_b b on a.id = b.a_id
where
    b.some_id = 123
    and b.create_date < '2014-01-01' 
    and b.create_date >= '2013-12-01'  
    and not exists (
        select
            a2.some_value
        from
            table_a a2
            join table_b b2 on a2.id = b2.a_id
        where
            b2.some_id = 123
            and b2.create_date < '2013-12-01'
    )

Whether this'll optimize to faster than a left join or not is something I can't think of right now...

score 0 · Answer 3 · edited May 23 '17 at 12:08

I'm not convinced a left join is the way to go but I believe it would look like this:

select *
from
     (
      select *
      from a
      where a.CreateDate >= '12/1/2013'
        and a.CreateDate < '1/1/2014')December
     left join(
               select *
               from b
               where b.CreateDate < '12/1/2013')PriorMonths on December.Value = PriorMonths.Value
where PriorMonths.Value is null

How about a "not exists" clause?

TechnetArticle

S/O question about In, Left Join, and Not Exists

select *
from a
where a.CreateDate >= '12/1/2013'
  and a.CreateDate < '1/1/2014'
  and not exists(
                 select *
                 from b
                 where b.CreateDate < '12/1/2013'
                   and b.[Value] = a.Value)

How to replace a complex SQL MINUS query with LEFT OUTER JOIN equivalent

3 Answers3

Linked