sql中的union子句

Question

给出下表说明：

emp :

ename varchar2(25)


dept:

loc varchar2(25)

我有以下3种情况：

select ename,to_number(null) from emp
union
select to_char(null),loc from dept;

  select ename,to_number(null) from emp
    union
    select to_number(null),loc from dept;

  select ename,null from emp
    union
    select null,loc from dept;

情况1完美执行。

情况2无法执行。

情况3也能完美执行。

我的理解是，我们需要确保如果没有对应的联合列，那么我们需要创建一个等效的数据类型哑元。因此，在其他表的相应列列表中number具有类似内容的类型to_number(null)。

在情况3中，我们将所有值都指定为null，因此我对此情况有所了解。但是在情况1中，loc和之间没有数据类型的兼容性to_number，但是它会执行。另一方面，情况2失败。请说清楚。

Answer 1

这个答案可能有点漫不经心…

Oracle对集合操作 非常 挑剔。每列必须与第二，第三等查询中的相应数据类型具有相同的数据类型。

我 觉得 你的第二个查询，因为甲骨文求值失败to_number()多项 之前 执行到union，但评估它与“空性” 之后。您的第一个查询成功，因为第一个值已被评估为“null-ness”，然后union出现。这意味着评估顺序为：

1. 第一选择功能
2. 第一选择数据类型
3. 第二选择功能
4. 联盟
5. 第二选择数据类型

我将尝试逐步证明这一点，但我不确定它是否可以构成绝对证明。

以下两个查询

select 1 from dual union select '1' from dual;
select '1' from dual union select 1 from dual;

由于未发生任何隐式转换，因此将失败并显示以下错误。

ORA-01790：表达式必须具有与相应表达式相同的数据类型

但是，以下两项都将成功

select null from dual union select '1' from dual;
select null from dual union select 1 from dual;

如果我们选择dump这两个查询中的，则会返回以下内容：

SQL> select dump(a)
  2    from ( select null a from dual union select '1' from dual );

DUMP(A)
-------------------------------------------------------------------

Typ=96 Len=1: 49
NULL

SQL> select dump(a)
  2    from ( select null a from dual union select 1 from dual );

DUMP(A)
-------------------------------------------------------------------

Typ=2 Len=2: 193,2
NULL

如您所见，这些列具有不同的数据类型。第一个查询包含一个字符，返回一个a
char，第二个查询返回一个数字，但是顺序已被调换，第二个查询select排在第一位。

最后，如果我们看dump您的第一个查询

SQL> select substr(dump(ename),1,35) a, substr(dump(loc),1,35) b
  2    from ( select ename,to_number(null) as loc from emp
  3            union
  4           select to_char(null),loc from dept
  5                  );

A                                   B
----------------------------------- -----------------------------------
Typ=1 Len=6: 104,97,104,97,104,97   NULL
NULL                                Typ=1 Len=6: 104,97,104,97,104,97

SQL>

您可以看到它dump(to_number(null))为空；但varchar2不会char返回a，因为这是您列的数据类型。有趣的是，返回的语句的顺序没有被颠倒，并且如果您要将该查询创建为表，则两列都将是varchar2。

在选择查询中确定列的数据类型时，Oracle会采用第一个已知数据类型，然后使用该数据类型来计算总体数据类型。这就是为什么第一个select为空的查询的行被反转的原因。

您的第一个查询成功，因为第一个选择select ename,to_number(null) from emp“描述了”结果集的外观。|varchar2|null|。然后，第二个查询添加|varchar2|varchar2|，这不会造成任何问题。

您的第二个查询失败，因为第一个选择select ename,to_number(null) from emp将结果集“描述”为varchar2, null。但是，您然后尝试在中添加一个空数字和一个varchar2 union。

信念的飞跃在于，Oracle决定该to_number(null)数字 在之前 ，union而不是 在
此之后才对其进行“空值”评估。我真的不知道如何测试这种情况是否真的发生，因为您无法创建带有null列的对象，并且您也不能选择它。

由于无法证明Oracle不允许的内容，我将尝试提供经验证据。考虑以下查询的结果（或错误）。

SQL> select 1 as a from dual union select to_number(null) from dual;

         A
----------
         1


SQL> select '1' as a from dual union select to_number(null) from dual;
select '1' as a from dual union select to_number(null) from dual
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL> select 1 as a from dual union select to_char(null) from dual;
select 1 as a from dual union select to_char(null) from dual
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL> select '1' as a from dual union select to_char(null) from dual;

A
-
1

它们似乎证明了to_char和to_number，无论是否对null进行隐式定义的数据类型，然后在union对其进行“ null-ness”评估之前先对数据类型进行适当性评估。

该解释也将涵盖该coalesce问题，因为在to_number(null)为null 之前 它是一个数字。