union clause in sql

Question

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.

Answer 1

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:

1. 1st select functions
2. 1st select data-types
3. 2nd select functions
4. union
5. 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.