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?
source to share
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
source to share