union clause in sql

oracle, sql, union

Solution

This answer may be a little rambling...

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.

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:

- 1st select functions

- 1st select data-types

- 2nd select functions

- union

- 2nd select data-types

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

Both the following queries

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: 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;

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

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.

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>

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`.

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.

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.

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`.

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.

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

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"

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

Problem

given the following table descriptions: ``` emp : ename varchar2(25) dept: loc varchar2(25) ``` I have following 3 cases: ``` 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; ``` case 1 executes perfectly. case 2 fails to execute. case 3 executes perfectly as well. 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. 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.

Original source