sql中的union子句 [英] union clause in sql

问题描述

给出以下表格说明:

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执行得很好.

---

case 1 executes perfectly.

情况2无法执行.

情况3也能完美执行.

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

My understanding is that we need to ensure that if there is no corresponding column for the union then we need to create an equivalent data type dummy. Hence, a number type to have something like to_number(null) in the corresponding column list of the other table.

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

In case 3, we specify all null, so I somewhat understand the scenario. But in case 1, there is no data type compatability between loc and to_number,but it executes. On the other hand, case 2 fails. Please clarify.

推荐答案

这个答案可能有点混乱...

This answer may be a little rambling...

Oracle对设置的操作非常挑剔.每列的数据类型必须与第二，第三等查询中的相应数据类型相同.

Oracle is very picky with set operations. Every column must have the same data-type as the corresponding ones in the second, third etc queries.

我认为您的第二次查询失败，因为Oracle在执行union之前将to_number()评估为数字 ，但将其评估为空值" 之后.您的第一个查询成功，因为第一个值已被评估为"null-ness"，然后出现union.这意味着评估顺序为:

I think your second query fails because Oracle evaluates to_number() as a number prior to performing the union but evaluates it for "null-ness" after. Your first query succeeds because the first value has been evaluated for "null-ness" and then the union occurs. This implies that the order of evaluation is:

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

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

I'll attempt to prove this step-by-step but I'm not sure it'll amount to an absolute proof.

以下两个查询

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

将失败，并出现以下错误，因为未进行任何隐式转换.

will fail with the following error as no implicit conversion takes place.

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

ORA-01790: expression must have same datatype as corresponding expression

但是，以下两项都将成功

However, both the following will succeed

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

如果我们选择这两个查询的 dump 返回以下内容:

If we select the dump of these two queries the following gets returned:

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

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

As you can see the columns have different data-types. The first query, with a character, returns a char and the second returns a number, but the order has been turned around, with the second select coming first.

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

Lastly, if we look at dump of your first query

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，因为这是您列的数据类型.有趣的是，返回的语句的顺序没有被反转，并且如果您要将该查询创建为表，则两列都是varchar2.

You can see that dump(to_number(null)) is null; but a varchar2 not a char is being returned, because this is the data-type of your column. It's interesting to note that the order of the returned statements has not been reversed and that if you were to create this query as a table both columns would be a varchar2.

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

When deciding the data-type of a column in a select query Oracle takes the first known data-type and then uses that to calculate the overall data-type. This would be why the queries where the first select was null had their rows reversed.

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

Your first query succeeds because the first select, select ename,to_number(null) from emp, "describes" what the result set is going to look like. |varchar2|null|. The second query then adds, |varchar2|varchar2|, which causes no problems.

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

Your second query fails because the first select select ename,to_number(null) from emp "describes" the result set as varchar2, null. However, you then try to add a null number and a varchar2 in the union.

这里的信念飞跃是Oracle决定to_number(null)是union之前的 ，直到以后才评估其为空".我真的不知道如何测试这种情况是否真的发生，因为您无法使用null列创建对象，并且您也不能选择它.

The leap of faith here is that Oracle is deciding that to_number(null) is a number prior to the union and not evaluating it for "null-ness" until after. I don't really know how to test whether this is actually happening as you can't create an object with a null column and as you note you can't select it either.

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

As I can't prove something that Oracle disallows I'll try for empirical evidence. Consider the results (or errors) of the following queries.

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中对其进行评估. 虚无"

They appear to demonstrate that to_char and to_number, no matter whether they're performed on a null implicitly define a data-type which is then evaluated for it's suitableness in a union, prior to their evaluation for "null-ness"

此说明还将涵盖coalesce问题，因为to_number(null)是之前的数字 是空的.

This explanation would also cover the coalesce issue as the to_number(null) is a number before it is a null.

这篇关于sql中的union子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！