sql - 如何用等效的 LEFT OUTER JOIN 替换复杂的 SQL MINUS 查询

Question

试图弄清楚如何用等效的左外连接替换以下内容:

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' 

不能做“NOT IN”，因为第二个查询有太多数据。

Answer 1

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