Union в 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 выполняется отлично.

случай 2 не выполняется.

случай 3 отлично работает.

Я понимаю, что мы должны убедиться, что если для объединения нет соответствующего столбца, нам нужно создать эквивалентный тип данных. Следовательно, тип number имеет что-то вроде to_number(null) в соответствующем списке столбцов другой таблицы.

В случае 3 мы укажем все значение null, поэтому я несколько понимаю сценарий. Но в случае 1 нет совместимости типов данных между loc и to_number, но он выполняется. С другой стороны, случай 2 терпит неудачу. Просьба уточнить.

Ответ 1

Этот ответ может немного расплыться...

Oracle очень разборчива с заданными операциями. Каждый столбец должен иметь тот же тип данных, что и соответствующие во втором, третьем и т.д..

Я думаю, что ваш второй запрос завершился неудачно, потому что Oracle оценивает to_number() как число до выполнения union, но после этого оценивает его значение "null-ness" . Ваш первый запрос завершается успешно, потому что первое значение было оценено для "null-ness" , а затем происходит union. Это означает, что порядок оценки:

Функции первого выбора
1-й выбор типов данных
Функции второго выбора
объединение
2-й выбор типов данных

Я попытаюсь доказать это шаг за шагом, но я не уверен, что это будет абсолютным доказательством.

Оба следующих запроса

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)) имеет значение null; но возвращается varchar2 не a char, потому что это тип данных вашего столбца. Интересно отметить, что порядок возвращаемых операторов не был отменен и что, если вы должны были создать этот запрос в качестве таблицы, оба столбца были бы varchar2.

При выборе типа данных столбца в выбранном запросе Oracle берет первый известный тип данных и затем использует его для вычисления общего типа данных. Вот почему запросы, в которых первый select был пустым, изменили свои строки.

Второй запрос завершился неудачно, потому что первый select select ename,to_number(null) from emp описывает "результат" как varchar2, null. Однако затем вы пытаетесь добавить нулевой номер и varchar2 в union.

Скачок веры здесь заключается в том, что Oracle решает, что to_number(null) является числом до union и не оценивает его для "null-ness" до следующего. Я действительно не знаю, как проверить, действительно ли это происходит, поскольку вы не можете создать объект с столбцом 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, независимо от того, выполняются ли они на ядре, неявно определяют тип данных, который затем оценивается для его соответствия в union, до их оценки для "нуль-Несс"

Это объяснение также будет охватывать проблему coalesce, поскольку to_number(null) - это число, прежде чем оно будет равно null.