外连接两列并在T-SQL中跳过Null

I have to left outer join on either column A or B and not on NULL

我必须在A列或B列上留下外连接,而不是NULL

So I'm not sure how I would do this.

所以我不确定我会怎么做。

If it was just the case of skipping NULL I could put a where clause on the join select but as I need both columns that doesn't work.

如果只是跳过NULL的情况我可以在连接选择上放置一个where子句,但因为我需要两个不起作用的列。

Here is a example of what I'm trying to accomplish:

这是我想要完成的一个例子:

**MainTable**

| Mobile |  Email  | MoreInfo |
|  1234  |         |    a     |
|  1234  |         |    b     |
|        | c@c.com |    c     |
|  9999  |         |    d     |
|  4321  |         |    e     |
|        | a@a.com |    f     |

**ReferenceTable**

| Mobile |  Email  | Id |
|  1234  |         | 1  |
|  4321  |         | 2  |
|        | a@a.com | 3  |
|        | b@b.com | 4  |

SELECT mt.MoreInfo, rt.Id
FROM MainTable mt
LEFT OUTER JOIN ReferenceTable rt ON mt.Mobile = rt.Mobile OR mt.Email = rt.Email

What I want to get out of this is:

我想要摆脱的是:

| MoreInfo |  Id  |
|    a     |   1  |
|    b     |   1  |
|    c     | NULL |
|    d     | NULL |
|    e     |   2  |
|    f     |   3  |

But will definitely get a value in C and D as well as their null or empty values in email or mobile will match with such values in the reference table.

但肯定会在C和D中获得一个值,以及它们在电子邮件或移动设备中的空值或空值将与参考表中的此类值匹配。

EDIT

I want to note that ReferenceTable will always have either Mobile or Email but never both. As given in the example however the MainTable might not have a match in ReferenceTable hence the outer join.

我想要注意的是,ReferenceTable将始终具有移动或电子邮件,但从不两者兼而有之。然而,如示例中所示,MainTable可能在ReferenceTable中没有匹配,因此外部联接。

Fiddle here

2 个解决方案

#1

Modified version of Dimt answer with protection from comparing empty string

Dimt的修改版本回答了保护,无法比较空字符串

SELECT mt.MoreInfo, rt.Id
    FROM MainTable mt
    LEFT OUTER JOIN ReferenceTable rt 
    ON (mt.Mobile = rt.Mobile AND coalesce(rt.Mobile, '')<>'') 
       OR (mt.Email = rt.Email AND coalesce(rt.Email, '')<>'')

#2

What's about putting additional condition for eliminating null or empty values such as

什么是为了消除空值或空值等附加条件

   SELECT mt.MoreInfo, rt.Id
    FROM MainTable mt
    LEFT OUTER JOIN ReferenceTable rt 
    ON (mt.Mobile = rt.Mobile AND rt.Mobile IS NOT NULL) OR (mt.Email = rt.Email AND rt.Email IS NOT NULL)

#1