与 Oracle 中的 INTERSECT 相对

Opposite of INTERSECT in Oracle

提问by Ula Krukar

I have two selects and I want to combine them in such a way, that only rows unique in both selects are returned. Is there any built-in way in Oracle 10g to achieve this?

我有两个选择，我想以这种方式组合它们，只返回两个选择中唯一的行。Oracle 10g 中是否有任何内置方法可以实现这一点？

I know I can do something like this:

我知道我可以做这样的事情：

(select1 UNION select2)
MINUS
(select1 INTERSECT select2)

but I would like to avoid it. Both select1and select2have 20 lines, so this way would be really obscure and difficult to maintain.

但我想避免它。双方select1并select2有20条线，所以这种方式将非常模糊，难以维持。

采纳答案by Luká? Lalinsky

If both select1and select2return no duplicates, you can use something like this:

如果同时select1并select2返回没有重复，你可以使用这样的事情：

SELECT * FROM (select1 UNION ALL select2) a
GROUP BY a.col1, a.col2, ...
HAVING count(*) = 1

回答by SQB

In Oracle 10g, you've got Common Table Expressions at your disposal.

在 Oracle 10g 中，您可以使用公共表表达式。

WITH
  select_1 AS (
    SELECT *
    FROM your_table
    WHERE your_condition = 1
  ),
  select_2 AS (
    SELECT *
    FROM your_other_table
    WHERE your_other_condition = 1
  )
SELECT * FROM select_1
UNION
SELECT * FROM select_2
MINUS
(
  SELECT * FROM select_1
  INTERSECT
  SELECT * FROM select_2
);

This keeps your subqueries maintainable and the purpose of your final query clear.

这使您的子查询保持可维护性，并使最终查询的目的明确。

Of course, having Oracle add a SYM_DIFFERENCEoperator to SQL would be even better, but I'm not holding my breath — they're still not convinced a BOOLEANdatatype would be a good idea.

当然，让 OracleSYM_DIFFERENCE为 SQL添加一个运算符会更好，但我并没有屏住呼吸——他们仍然不相信BOOLEAN数据类型是个好主意。

回答by Thorsten

Here's another idea:

这是另一个想法：

• Do a full outer join of select1 and select2
• Use only those records with select1.id = NULL (record is only in select2) or select2.ID = NULL (record is only in select1)

• 对 select1 和 select2 进行全外连接
• 仅使用 select1.id = NULL（记录仅在 select2 中）或 select2.ID = NULL（记录仅在 select1 中）的那些记录

like this:

像这样：

SELECT *
FROM select1 FULL OUTER JOIN select2 on select1.id = select2.id
WHERE select1.id is null or select2.id is null

回答by Margaret

This worked for me- not sure how fast it is.

这对我有用 - 不确定它有多快。

(select table_name from dba_tables where user = 'X'
union
select table_name from dba_tables where user = 'Y')
minus
(select table_name from dba_tables where user = 'X'
intersect
select table_name from dba_tables where user = 'Y')

回答by kc-

Here's another solution, this time using the count() analytic (Oracle 10 or later).

这是另一个解决方案，这次使用 count() 分析（Oracle 10 或更高版本）。

Advantages:

好处：

• we can specify which columns to EXTRASECT on (e.g. KK1, KK2 in the example).
• we can select non-key columns (e.g. NK1, NK2... in the example) which we don't require to match.
• efficient plan.
• Similar to the FULL OUTER JOIN example, but we don't get the key columns as separate fields needing decode or case to fold them together.

• 我们可以指定要在哪些列上进行 EXTRASECT（例如，示例中的 KK1、KK2）。
• 我们可以选择不需要匹配的非键列（例如示例中的 NK1、NK2...）。
• 有效的计划。
• 类似于 FULL OUTER JOIN 示例，但我们不会将键列作为需要解码或大小写的单独字段将它们折叠在一起。

select KK1, KK2, NK1, NK2 from ( select KK1, KK2, NK1, NK2, count( * ) over( partition by KK1, KK2 ) cnt from ( select KK1, KK2, NK1, NK2 from X union all select KK1, KK2, NK1, NK2 from Y ) ) where cnt = 1;

select KK1, KK2, NK1, NK2 from ( select KK1, KK2, NK1, NK2, count( * ) over( partition by KK1, KK2 ) cnt from ( select KK1, KK2, NK1, NK2 from X union all select KK1, KK2, NK1, NK2 from Y ) ) where cnt = 1;

回答by cesar

-- get not intersect data
SELECT_FINAL
WHERE FIELD_PK IS NOT IN(
    -- get ids of intersect
    SELECT_AUX FIELD_PK1 FROM (
        SELECT1
        INTERSECT
        SELECT2
     )
)

i do it

我做