sql - 有或没有不同的 oracle 交集

Question

我有以下查询:

select id from t1
intersect
select id from t2
intersect
select id from t3

id 在某些表中可能不是唯一的，所以我需要使用 distinct。

一般来说什么比较好:

select distinct id from (
select id from t1
intersect
select id from t2
intersect
select id from t3)

或

select distinct id from t1
intersect
select id from t2 -- here id is unique
intersect
select distinct id from t3

Answer 1

不需要 DISTINCT。 INTERSECT 运算符自动生成一组不同的值。正如您在此示例中看到的，x 有两行的 ID 为 1，y 只有一行的 ID 1 的 和两者的 INTERSECTION 只产生一行

SQL> ed
Wrote file afiedt.buf

  1  with x as (select 1 id from dual union all select 1 from dual),
  2       y as (select 1 id from dual)
  3  select id
  4    from x
  5  intersect
  6  select id
  7*   from y
SQL> /

        ID
----------
         1

即使您将两行表的INTERSECT 与其自身相加，您仍然会在输出中得到一行

SQL> ed
Wrote file afiedt.buf

  1  with x as (select 1 id from dual union all select 1 from dual)
  2  select id
  3    from x
  4  intersect
  5  select id
  6*   from x
SQL> /

        ID
----------
         1