MySQL 联合两个具有不同列数的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2309943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unioning two tables with different number of columns
提问by Hyman Kada
I have two tables (Table A and Table B).
我有两个表（表 A 和表 B）。
These have different number of columns - Say Table A has more columns.
这些有不同数量的列 - 说表 A 有更多的列。
How can I union these two table and get null for the columns that Table B does not have?
如何联合这两个表并为表 B 没有的列获取空值？
回答by Kangkan
Add extra columns as null for the table having less columns like
为具有较少列的表添加额外的列作为空值，例如
Select Col1, Col2, Col3, Col4, Col5 from Table1
Union
Select Col1, Col2, Col3, Null as Col4, Null as Col5 from Table2
回答by Anand Varkey Philips
I came here and followed above answer. But mismatch in the Order of data type caused an error. The below description from another answer will come handy.
我来到这里并遵循上述答案。但是 Order of 数据类型不匹配导致了错误。以下来自另一个答案的描述会派上用场。
Are the results above the same as the sequence of columns in your table? because oracle is strict in column orders. this example below produces an error:
上面的结果是否与表中列的顺序相同？因为oracle在列顺序上很严格。下面的这个例子产生了一个错误：
create table test1_1790 (
col_a varchar2(30),
col_b number,
col_c date);
create table test2_1790 (
col_a varchar2(30),
col_c date,
col_b number);
select * from test1_1790
union all
select * from test2_1790;
ORA-01790: expression must have same datatype as corresponding expression
ORA-01790: 表达式必须与相应的表达式具有相同的数据类型
As you see the root cause of the error is in the mismatching column ordering that is implied by the use of * as column list specifier. This type of errors can be easily avoided by entering the column list explicitly:
如您所见，错误的根本原因在于使用 * 作为列列表说明符隐含的不匹配的列排序。通过明确输入列列表，可以轻松避免此类错误：
select col_a, col_b, col_c from test1_1790 union all select col_a, col_b, col_c from test2_1790; A more frequent scenario for this error is when you inadvertently swap (or shift) two or more columns in the SELECT list:
select col_a, col_b, col_c from test1_1790 union all select col_a, col_b, col_c from test2_1790; 此错误更常见的情况是当您无意中交换（或移动）SELECT 列表中的两列或更多列时：
select col_a, col_b, col_c from test1_1790
union all
select col_a, col_c, col_b from test2_1790;
OR if the above does not solve your problem, how about creating an ALIASin the columns like this: (the query is not the same as yours but the point here is how to add alias in the column.)
或者，如果上述方法不能解决您的问题，那么如何在这样的列中创建 ALIAS：（查询与您的不同，但这里的重点是如何在列中添加别名。）
SELECT id_table_a,
desc_table_a,
table_b.id_user as iUserID,
table_c.field as iField
UNION
SELECT id_table_a,
desc_table_a,
table_c.id_user as iUserID,
table_c.field as iField
回答by Lukasz Szozda
Normally you need to have the same number of columns when you're using set based operators so Kangkan's answeris correct.
通常，当您使用基于集合的运算符时，您需要具有相同数量的列，因此Kangkan 的答案是正确的。
SAS SQL has specific operator to handle that scenario:
SAS SQL 有特定的运算符来处理这种情况：
SAS(R) 9.3 SQL Procedure User's Guide
CORRESPONDING (CORR) Keyword
The CORRESPONDING keyword is used only when a set operator is specified. CORR causes PROC SQL to match the columns in table expressions by name and not by ordinal position. Columns that do not match by name are excluded from the result table, except for the OUTER UNION operator.
对应 (CORR) 关键字
CORRESPONDING 关键字仅在指定了集合运算符时使用。CORR 导致 PROC SQL 按名称而不是按顺序位置匹配表表达式中的列。名称不匹配的列将从结果表中排除，但 OUTER UNION 运算符除外。
SELECT * FROM tabA
OUTER UNION CORR
SELECT * FROM tabB;
For:
为了：
+---+---+
| a | b |
+---+---+
| 1 | X |
| 2 | Y |
+---+---+
OUTER UNION CORR
+---+---+
| b | d |
+---+---+
| U | 1 |
+---+---+
<=>
+----+----+---+
| a | b | d |
+----+----+---+
| 1 | X | |
| 2 | Y | |
| | U | 1 |
+----+----+---+
U-SQL supports similar concept:
U-SQL 支持类似的概念：
OUTER
requires the BY NAME clause and the ON list. As opposed to the other set expressions, the output schema of the OUTER UNION includes both the matching columns and the non-matching columns from both sides. This creates a situation where each row coming from one of the sides has "missing columns" that are present only on the other side. For such columns, default values are supplied for the "missing cells". The default values are null for nullable types and the .Net default value for the non-nullable types (e.g., 0 for int).
BY NAME
is required when used with OUTER. The clause indicates that the union is matching up values not based on position but by name of the columns. If the BY NAME clause is not specified, the matching is done positionally.
If the ON clause includes the “*” symbol (it may be specified as the last or the only member of the list), then extra name matches beyond those in the ON clause are allowed, and the result's columns include all matching columns in the order they are present in the left argument.
外
需要 BY NAME 子句和 ON 列表。与其他集合表达式相反，OUTER UNION 的输出模式包括两边的匹配列和非匹配列。这造成了这样一种情况，即来自一侧的每一行都有“缺失的列”，这些列仅存在于另一侧。对于此类列，将为“缺失单元格”提供默认值。可空类型的默认值为 null，不可空类型的默认值为 .Net（例如，0 表示 int）。
按名字
与 OUTER 一起使用时需要。该子句表明联合不是根据位置而是根据列的名称来匹配值。如果未指定 BY NAME 子句，则按位置进行匹配。
如果 ON 子句包含“*”符号（它可能被指定为列表的最后一个或唯一的成员），则允许超出 ON 子句中的额外名称匹配，并且结果的列包括列表中的所有匹配列它们出现在左参数中的顺序。
And code:
和代码：
@result =
SELECT * FROM @left
OUTER UNION BY NAME ON (*)
SELECT * FROM @right;
EDIT:
编辑：
The concept of outer union is supported by KQL:
KQL支持外联合的概念：
kind:
inner - The result has the subset of columns that are common to all of the input tables.
outer - The result has all the columns that occur in any of the inputs. Cells that were not defined by an input row are set to null.
种类：
内部 - 结果具有所有输入表共有的列的子集。
外部 - 结果包含任何输入中出现的所有列。未由输入行定义的单元格设置为空。
Example:
例子：
let t1 = datatable(col1:long, col2:string)
[1, "a",
2, "b",
3, "c"];
let t2 = datatable(col3:long)
[1,3];
t1 | union kind=outer t2;
Output:
输出：
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 1 | a | |
| 2 | b | |
| 3 | c | |
| | | 1 |
| | | 3 |
+------+------+------+
回答by Sai Sai
if only 1 row, you can use join
如果只有 1 行，则可以使用 join
Select t1.Col1, t1.Col2, t1.Col3, t2.Col4, t2.Col5 from Table1 t1 join Table2 t2;
