Oracle SQL - UNION ALL 返回行，即使它们不存在

Question

我有以下查询，它合并了两个较小的查询：

SELECT 'Static 1' type, somefield, some_id   
FROM (SELECT some_id, somefield
FROM sometable
WHERE someothercolumn = 'Static 1'
and someid = :id)
UNION ALL
SELECT 'Static 1' type, somefield, some_id   
FROM (SELECT some_id, somefield
FROM sometable
WHERE someothercolumn = 'Static 2'
and someid = :id)

如果查询返回一行，而查询 2 返回一行，那么我将有两行，如下所示：

type      |      somefield     |     some_id
---------------------------------------------
Static 1  |       somevalue    |      1
Static 2  |       somevalue    |      1

这是我想要的输出。如果这两个查询中的一个不返回一行(或者即使两个查询都不返回一行(，那么我将返回零行。这不是我想要的。我总是希望"类型"和"some_id"甚至返回 如果没有数据。因此，如果这两个查询都没有任何数据，我的预期结果将是：

type      |      somefield     |     some_id
---------------------------------------------
Static 1  |                    |      1
Static 2  |                    |      1

我试图将所有查询与不存在统一起来，但这样'some_id'将需要是一些任意硬编码值。我想要的是让它始终显示传递的绑定变量，:id.

Answer 1

也许是这样的：

WITH a AS (/* subquery 1 */),
b AS (/* subquery 2 */)
SELECT (SELECT a.col1 FROM a),
(SELECT a.col2 FROM a),
...
FROM dual
UNION ALL
SELECT (SELECT b.col1 FROM b),
(SELECT b.col2 FROM b),
...
FROM dual

Answer 2

一种选择是选择带有NOT EXISTS的标题虚拟行，以决定是否返回它(是的，如果表中的实际行不存在;否，如果行存在(。

看看这个例子：参见目前被注释的 #4 行，这意味着你的表不包含满足该条件的行，因此将显示一个虚拟标题行：

SQL> with test (type, some_id) as
2    (select 'Static 99', 44 from dual union all
3     select 'Static 2' , 57 from dual
4     --union all select 'Static 1', 66 from dual   -- toggle to see the difference
5    )
6  -- This is a header which won't be displayed if table contains
7  -- type = 'Static 1' and some_id = 66
8  select 'Static 1' type, 1 some_id
9  from dual
10  where not exists (select null
11                    from test
12                    where type = 'Static 1'
13                      and some_id = 66
14                   )
15  union all
16  -- This returns actual rows (if they exist)
17  select 'Static 1' type, some_id
18  from (select some_id
19        from test
20        where type = 'Static 1'
21          and some_id = 66
22       );
TYPE        SOME_ID
-------- ----------
Static 1          1
SQL>

但是，如果它存在(第 #4 行未注释(，则显示实际数据，而不显示该虚拟标题行：

SQL> with test (type, some_id) as
2    (select 'Static 99', 44 from dual union all
3     select 'Static 2' , 57 from dual
4     union all select 'Static 1', 66 from dual   -- toggle to see the difference
5    )
6  -- This is a header which won't be displayed if table contains
7  -- type = 'Static 1' and some_id = 66
8  select 'Static 1' type, 1 some_id
9  from dual
10  where not exists (select null
11                    from test
12                    where type = 'Static 1'
13                      and some_id = 66
14                   )
15  union all
16  -- This returns actual rows (if they exist)
17  select 'Static 1' type, some_id
18  from (select some_id
19        from test
20        where type = 'Static 1'
21          and some_id = 66
22       );
TYPE        SOME_ID
-------- ----------
Static 1         66
SQL>

如果这样做符合您的要求，请将相同的原则应用于您正在使用的第二个select(以Static 2(。

Answer 3

为您的查询创建一个 CTE，为另外 2 行制作另一个 CTE，并使用 UNION ALL 并且不存在它，如下所示：

WITH 
cte1 AS (
<your query here>
),
cte2 AS (
SELECT 'Static 1' type, null somefield, :id FROM dual
UNION ALL
SELECT 'Static 2' type, null somefield, :id FROM dual 
)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
WHERE NOT EXISTS (SELECT 1 FROM cte1)