SQL:两个不具有完整列匹配项的表的并集 [英] SQL: Union of two tables which don't have full column match

问题描述

我有一个table_A，它具有一组列A1，A2和一个table_b，它具有一组列B1，B2

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

碰巧是A2=B1，但是其余的列不匹配(并且不应该匹配).我想追加表格，所以我使用UNION ALL

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

对于不匹配的列，我在UNION语句的两侧使用null as COLUMN_NAME

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;

会输出以下错误:

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

是因为空值吗?

推荐答案

您需要在上层SELECT中将NULL显式转换为适当的类型.

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;

---

关于 @evilive 的替代方案，您可以将固定值用作VARCHAR的空字符串('')或将NUMBER用作零，但我认为显式强制转换是更好的解决方案，因为它很明显不会引起意外

---

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

这篇关于SQL:两个不具有完整列匹配项的表的并集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！