如何用等效的 LEFT OUTER JOIN 替换复杂的 SQL MINUS 查询 [英] How to replace a complex SQL MINUS query with LEFT OUTER JOIN equivalent
本文介绍了如何用等效的 LEFT OUTER JOIN 替换复杂的 SQL MINUS 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
试图找出如何用等效的左外连接替换以下内容:
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'
不能做NOT IN",因为第二个查询有太多数据.
Can not do "NOT IN", as the second query has too much data.
推荐答案
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
这篇关于如何用等效的 LEFT OUTER JOIN 替换复杂的 SQL MINUS 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文