SQL: joining two tables that do not have a complete column match
I have table_A
one which has a set of columns A1
, A2
and table_b which has a set of columns B1
,B2
It happens that A2=B1
, but the rest of the columns do not match (and should not). I would like to add a table, so I useUNION ALL
For non-matching columns, I use null as COLUMN_NAME
on either side of the operatorUNION
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 throws 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 zeros?
You need to explicitly specify NULL for the respective types at the top 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 alternatives like @evilive , you can use fixed values like empty string ( ''
) for VARCHAR, or zero for NUMBER, but for my opinion, explicit casting is the best solution because it's obvious and won't cause any surprises
SQLFiddle