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

outer-join, sql

Solution

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

Problem

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.

Original source