SQL: Union of two tables which don't have full column match

Question

I have a table_A which has a set of column A1,A2 and a table_b which has a set of columns B1,B2

It happens that A2=B1 but the rest of the columns don't match (and are not supposed to). I would like to append the table so I use UNION ALL

For non matching columns, I use null as COLUMN_NAME, on both sides of the UNION statement

CREATE VIEW MY_VIEW AS 
SELECT
TABLE_A.A1,
TABLE_A.A2,
null as B2
from TABLE_A
union all
SELECT 
null as A1,
TABLE_B.B1 as A2,
TABLE_B.B2 as B2
from TABLE_B;

which outputs the following error:

Error report: SQL Error: ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression"

Is it because of the nulls?

Answer 1

You need to explicitly cast NULLs to appropriate types in the upper SELECT.

CREATE VIEW MY_VIEW AS 
SELECT
TABLE_A.A1,
TABLE_A.A2,
CAST(null AS <type_of_TABLE_B_B2>) as B2
from TABLE_A
union all
SELECT 
null,
TABLE_B.B1,
TABLE_B.B2
from TABLE_B;

---

As for the alternatives as @evilive says you can use fixed values as empty string ('') for VARCHARs or zero for NUMBERs but for my opinion explicit cast is better solution because it is obvious and will not cause a surprises

SQLFiddle