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种情况下，我们指定了all null，所以我有点理解这个场景。但是在第1种情况下，loc和to_number之间没有数据类型可压缩性，但是它会执行。另一方面，案例2失败了。请澄清。

Answer 1

答案可能有点杂乱无章..。

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

我认为您的第二个查询失败了，因为Oracle在执行to_number()之前将union计算为一个数字，但之后将其计算为"null-ness“。您的第一个查询成功，因为第一个值已被计算为"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

如您所见，这些列都有different data-types。第一个查询带有一个字符，返回一个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))是null；但是返回的是varchar2而不是char，因为这是列的数据类型。值得注意的是，返回语句的顺序没有颠倒，如果要将此查询创建为表，则两个列都是varchar2。

在决定select查询中列的数据类型时，Oracle接受第一个已知的数据类型，然后使用该类型计算整个数据类型。这就是为什么第一个select为null的查询会反转它们的行。

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

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

这里的信念的飞跃是，甲骨文决定to_number(null)是union之前的一个数字，直到之后才计算它的“空值”。我真的不知道如何测试这是否真的发生了，因为您不能创建一个带有null列的对象，而且您也不能选择它。

因为我不能证明甲骨文不允许的东西，所以我会尝试寻找经验证据。考虑以下查询的结果(或错误)。

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，无论它们是否在空上执行，都隐式地定义了数据类型，然后在计算“空”之前，计算数据类型在union中是否合适。

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