如何用另一个表中的信息替换空数据？

Question

Order_ID  ship_state  bill_state 
    121   null   CA 
    122   WA    IN  
    123   CA    OR 
    124   null   FL 

我有一个查询（下面），带回这些结果（上面）。 Order_ID，Ship_state和bill_state分别位于不同的表中。我如何修改我的查询，以便它将填充与相应的bill_state空null_state？有些订单直接发送到帐单地址，这使得运输字段为空，因为它们留空。我想要生成一份船舶状态清单。

SELECT ca.STATE AS ship_state, 
     co.ID, 
     co.CUSTOMER_ID, 
     ca.ADDR_NO, 
     co.SHIP_TO_ADDR_NO, 
     c.STATE AS Bill_state, 
     c.NAME AS Bill_name, 
     ca.NAME AS Ship_name, 
     col.PART_ID, 
     col.ORDER_QTY, 
     ca.COUNTRY AS Ship_Country, 
     c.COUNTRY AS Bill_country, 
     co.ORDER_DATE 
FROM dbo.CUST_ORDER_LINE AS col 
FULL OUTER JOIN dbo.CUST_ADDRESS AS ca 
    FULL OUTER JOIN dbo.CUSTOMER AS c 
     INNER JOIN dbo.CUSTOMER_ORDER AS co 
      ON c.ID = co.CUSTOMER_ID 
     ON ca.CUSTOMER_ID = co.CUSTOMER_ID 
     AND ca.ADDR_NO = co.SHIP_TO_ADDR_NO 
     AND ca.CUSTOMER_ID = c.ID 
    ON col.CUST_ORDER_ID = co.ID 
WHERE co.ORDER_DATE > '2014-1-1' 
    AND co.ID NOT LIKE 'rma%' 
ORDER BY co.ID 

Answer 1

可以使用COALESCE功能：

在你SELECT：

COALESCE(ca.STATE, c.STATE) AS ship_state 

这将返回的c.STATE如果ca.STATE是价值NULL。

Answer 2

使用ISNULL功能，

ISNULL(ca.State, c.State) As ship_state