时间戳

首页 > 解决方案 > 如何在具有 NULL 的列上连接数据集？

sql - 如何在具有 NULL 的列上连接数据集？

问题描述

我有两张桌子，看起来像这样：

Table_1:

Shop_ID | Offer_ID | Metric_1
--------|----------|---------
AAA     | 111      | 1
AAA     | 222      | 2
BBB     | 111      | 3
BBB     | 222      | 4

Table 2:

Shop_ID | Offer_ID | Metric_2
--------|----------|---------
AAA     | 111      | 1
AAA     | 222      | 2
BBB     | 111      | 3
BBB     | 222      | 4
CCC     | 111      | 5
CCC     | 222      | 6

我想将它们加入一个组合数据集，如下所示：

Shop_ID | Offer_ID | Metric_1 | Metric_2
--------|----------|----------|---------
AAA     | 111      | 1        | 1
AAA     | 222      | 2        | 2
BBB     | 111      | 3        | 3
BBB     | 222      | 4        | 4
CCC     | 111      | NULL     | 5
CCC     | 222      | NULL     | 6

请问有人知道怎么做吗？我尝试了以下代码，但它只是给了我一条错误消息，说“不允许将 NULL 值分配给 NOT NULL 列”。

  INSERT INTO TABLE_3
  (
    SELECT  TABLE_2.SHOP_ID,
        TABLE_2.OFFER_ID,
        METRIC_1,
        METRIC_2
    FROM    TABLE_1
    FULL OUTER JOIN
        TABLE_2 
    ON  TABLE_1.SHOP_ID = TABLE_2.SHOP_ID
    AND TABLE_1.OFFER_ID = TABLE_2.OFFER_ID
    );

标签： sqljoindb2

解决方案

You are getting this error because the column Metric_1 has a NOT NULL condition. You can do two things about this.

  1. Alter the TABLE_3 Metric_1 column to allow NULL values

    ALTER TABLE TABLE_3
     ALTER COLUMN Metric_1 *your_data_type* NULL

  2. Use the ISNULL expression. This is where you basically replace NULL values with something else. In the example below I used -1, but you can use whatever makes the most sense for you.

    INSERT INTO TABLE_3   (
          SELECT  TABLE_2.SHOP_ID,
              TABLE_2.OFFER_ID,
              ISNULL(METRIC_1,-1),
              METRIC_2
          FROM    TABLE_1
          FULL OUTER JOIN
              TABLE_2 
          ON  TABLE_1.SHOP_ID = TABLE_2.SHOP_ID
          AND TABLE_1.OFFER_ID = TABLE_2.OFFER_ID
          );

推荐阅读