sql - 在 sql join 中不包括空值

Question

我有两个表 CustomerAddress(CustomerId, City, Country) 和 CustomerTransactions(TransactionId, CustomerId, CustomerContact)。以下是表中的值:

对于客户地址:

1001, El Paso, USA    
1002, Paris, France    
1003, Essen, Germany    

对于客户交易:

98, 1001, Phillip    
99, 1001, NULL
100, 1001, NULL    
101, 1003, Carmen    
102, 1003, Carmen    
103, 1003, Lola    
104, 1003, NULL    
105, 1002, NULL

我正在尝试连接两个表并获得以下结果集:

1001, El Paso, USA, Phillip    
1002, Paris, France, (empty string)    
1003, Essen, Germany, Carmen    
1003, Essen, Germany, Lola

这似乎是一个简单的连接，但我在得出上述结果集时遇到了问题。请帮忙。

谢谢。

Answer 1

我终于明白了...

SELECT DISTINCT CA.CustomerId, CA.CustomerCity, CA.CustomerCountry, ISNULL(CT.CustomerContact) AS CustomerContact
FROM CustomerAddress CA
LEFT JOIN (SELECT CustomerId, CustomerContact 
           FROM CustomerTransactions
           WHERE CustomerContact IS NOT NULL) CT ON CT.CustomerID = CA.CustomerID

谢谢你让我走上正轨。