SQL - 如何使用另一个表中的值将两个表连接到任一表中的缺失值或空值

Alright *, I have a problem:

好吧*,我有一个问题:

I am doing some work with Azure Machine Learning and I have reached an impasse. I have two tables, and I need to join them. The tables look like this:

我正在使用Azure机器学习做一些工作,我陷入了僵局。我有两张桌子,我需要加入他们。表格如下所示:

   TABLE A          TABLE B   
+-----------+    +-----------+
| a | b | c |    | a | b | c |
+-----------+    +-----------+
| 1 | 2 |   |    |   | 2 | 3 |
+-----------+    +-----------+

(those are just examples.)

(这些只是一些例子。)

I need to join these tables when columns they share (in this case only b, but could be multiple) are equivalent. I also, however, need to populate missing values. If TABLE A is missing a value for one of its columns, and TABLE B has it for a matching row, they should combine values in the result table. I know that there is a way to do this one way, but it also needs to work in reverse, so that if TABLE B is missing a value, and TABLE A has one, it is populated.

我需要在它们共享的列(在这种情况下只有b,但可能是多个)相同时加入这些表。但是,我也需要填充缺失的值。如果表A缺少其中一个列的值,并且表B将其用于匹配的行,则它们应该组合结果表中的值。我知道有一种方法可以单向执行,但它也需要反向工作,这样如果表B缺少一个值,而表A有一个,则填充它。

EDIT: Desired Result:

编辑:期望的结果:

   TABLE C
+-----------+
| a | b | c |
+-----------+
| 1 | 2 | 3 |
+-----------+

Some background information:

一些背景资料:

AzureML uses a form of SQLite for their SQL interpretation, so please try and keep your answers in as basic SQL as possible. Thanks! :)

AzureML使用SQLite的形式进行SQL解释,因此请尽量将您的答案保留为基本SQL。谢谢! :)

AzureML has a built in join module, for those familiar with AzureML, but I don't think it'll be able to accomplish what is necessary. I'll use the SQL interpretation module.

对于熟悉AzureML的人来说,AzureML有一个内置的连接模块,但我认为它不能完成必要的工作。我将使用SQL解释模块。

Your assistance is appreciated! Thanks!

非常感谢您的帮助!谢谢!

1 个解决方案

#1

Answering my own question:

回答我自己的问题:

It turned out the join type I needed was a Full Outer Join.

原来我需要的连接类型是Full Outer Join。

Background information:

For those pursuing AzureML related to this question in the future, I had to enable the functionality to save columns from the 'Right' table input.

对于那些追求与此问题相关的AzureML的人,我必须启用从“右”表输入中保存列的功能。

I then ran this through a 'Project Columns' module and a 'Metadata' module to rename them to the form I desired.

然后我通过“项目列”模块和“元数据”模块运行它,将它们重命名为我想要的格式。

#1