UNION子句的替代方案

Question

我正在试着自学并更好地了解除了UNION之外的替代方法，并发现在什么情况下可以使用JOIN。

在尝试过程中，如果没有UNION似乎无法得到我想要的结果。是否可能用单个查询语句来编写它？

SELECT DISTINCT a.id
  FROM table1 a, table2 b
 WHERE a.id = b.id
       AND a.ind IS NULL
       AND b.year >= '2017'
       AND b.code IN ('01','02','03')
       AND b.flag NOT IN ('F','L')
UNION
SELECT DISTINCT a.id
  FROM table1 a, table3 c
 WHERE a.id = c.id
       AND a.ind IS NULL
       AND c.area = 'MAIN'
       AND SYSDATE >= c.start

非常感谢您提供的任何指导或帮助。

- Zilch

---

你现在拥有的是一个使用联合的单个查询。我认为另一种选择是将这三个表连接在一起，但是WHERE逻辑可能会变得非常丑陋（如果你不知道这是什么意思，它的意思是超出了丑陋的范畴）。 - Tim Biegeleisen

你的查询中不需要使用 DISTINCT，因为 UNION 会自动去除重复项。 - Magnus

你本可以提供一个带有一些测试数据的 SQL Fiddle。 - Gerard H. Pille

Answer 1

SELECT DISTINCT a.id
FROM table1 a
LEFT JOIN table2 b on b.id = a.id AND b.year >= '2017'
     AND b.code IN ('01', '02', '03') AND b.flag NOT IN ('F', 'L') 
LEFT JOIN table3 c ON a.id = c.id and c.area = 'MAIN' and SYSDATE >= c.start
WHERE a.ind IS NULL
    AND ( b.id IS NOT NULL or c.id IS NOT NULL)

这是其中一种情况，旧的过时的 A,B 连接语法真正显示了它的年龄。当你有一些条件必须放在特定的 ON 子句中，而其他条件必须放在 WHERE 子句中时，使用这种语法就会变得非常困难。按照完整的 INNER JOIN、LEFT JOIN 等方式编写查询语句总是更好的选择，这样编写后，以后阅读和理解起来也更容易。

- Joel Coehoorn

---

两个外连接后跟几个“不为空”的条件，将它们转换回内连接。为什么不呢？ - Gerard H. Pille

@GerardH.Pille 这被称为排除连接。这是一种常见的模式，工作得非常好，并且绝对不会将事物简单地转换回内部连接。通常的模式只需检查 IS NULL 而不是 IS NOT NULL，但在这种情况下，使用 OR 两次执行该模式结果仍然是正确的。 - Joel Coehoorn

一种排除连接！这证明了一个人永远不会太老去学习。我仍然认为你在浪费时间。 - Gerard H. Pille

Answer 2

每当你看到一个不同的东西时，都会浪费资源。

SELECT a.id
  FROM table1 a
  where a.ind IS NULL
    and (   exists (select null from table2 b
                      WHERE a.id = b.id
                        AND b.year >= '2017'
                        AND b.code IN ('01','02','03')
                        AND b.flag NOT IN ('F','L') )
         or exists (SELECT null FROM table3 c
                      WHERE a.id = c.id
                        AND c.area = 'MAIN'
                        AND SYSDATE >= c.startdt)
        )

在表2 (id、year、code、flag) 和表3 (id、area、startdt) 上的索引不会影响性能。Oracle 11gR2 不允许我使用名为“start”的列。

- Gerard H. Pille

---

我倾向于使用IN而不是EXISTS，因为它更短并且通常更易于阅读。但你必须小心并（几乎）总是使用NOT EXISTS。 - gpeche

@gpeche "几乎总是使用 NOT EXISTS"？ - Magnus

@GerardH.Pille - 你似乎太老了，不适合使用Oracle。你所描述的情况早已改变；至少从版本10开始，IN和EXISTS会产生相同的执行计划。 - user5683823

@Gerard H. Pille 自2001年以来使用 Oracle，迄今为止我已经很久没有看到使用IN与EXISTS的执行计划有任何差异。此外，请阅读 http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html:您应该考虑使用 WHERE EXISTS 或 WHERE IN。成本优化器（CBO）认为它们几乎相等 - 您可能也是这样认为的。和在两种情况下，优化器都会使用半连接，您无法指定但优化器可能执行（它在第一次命中后停止连接）。 - gpeche

@Magnus 阅读 https://dev59.com/GXVC5IYBdhLWcg3w1Exq 。基本上，IN 和 EXISTS 是“等效的”，但是在存在 NULL 的情况下，NOT IN 和 NOT EXISTS 的行为是不同的。在大多数情况下，“直观”的行为几乎总是由 NOT EXISTS 提供的。 - gpeche

Answer 3

我会使用这个。

select a.id
from table1 a
where
  a.ind is null and (
  a.id in (
    select b.id
    from table2 b
    where
    b.year >= '2017'
    and b.code IN ('01','02','03')
    and b.flag NOT IN ('F','L')  
  ) or
  a.id in (
    select c.id
    from table3 c
    where
    c.area = 'MAIN'
    and sysdate >= c.start
  )
)

DISTINCT + 表连接通常可以重写为 IN/EXISTS。在某些数据库中，它甚至可以选择更好的执行计划（例如：Oracle）。

- gpeche

Answer 4

请告诉我是否使用全外连接有意义

with cte as (
select 1 as nu union 
select 2 as nu union 
select 3 as nu union 
select 4 as nu union 
select 5 as nu union 
select 6 as nu )
,cte2 as (
select 1 as nu union 
select 2 as nu union 
select 3 as nu )
,cte3 as (
select 7 as nu union 
select 8 as nu union 
select 9 as nu )
select coalesce(cte.nu,cte2.nu,cte3.nu)
from cte
full outer join cte2 on cte.nu = cte2.nu
full outer join cte3 on cte.nu = cte3.nu

- SSMan

Answer 5

请检查一下这是否有效。

SELECT DISTINCT a.id
  FROM table1 a, table2 b, table3 c
 WHERE (a.id = b.id
        OR a.id = c.id)
       AND a.ind IS NULL
       AND (( b.year >= '2017'
       AND b.code IN ('01','02','03')
       AND b.flag NOT IN ('F','L'))
       OR (c.area = 'MAIN'
       AND SYSDATE >= c.start))

- Avani

---

这是浪费，你没有任何理由连接table2和table3，导致产生笛卡尔积。 - Gerard H. Pille