DB2 SQL Compare on JOIN

Question

我有以下数据：

TABLE1: 
ADDRESS               INTER1    INTER2
12345 E AVE STREET    44444     55555555
888 OtherStreet       44444     55555555

TABLE2: 
ADDRESS             INTER1  INTER2
12345 E AVE STREET  44444   55555555

我可能可以这样在代码中解决：

if(tblOneInterOne == tblTwoInterOne ) {
   // compare address                                   
}

，但希望在 SQL 中解决。

SELECT 
     A.ADDRESS
   , A.INTER1
   , A.INTER2
FROM TABLE1 AS A
    LEFT JOIN TABLE2 AS B
    ON A.INTER1 = B.INTER1
    AND A.INTER2 = B.INTER2
    AND A.ADDRESS <> B.ADDRESS

与上述SQL进行比较时，我仍然在我的数据结果上得到这个地址：12345 E AVE STREET。我还尝试过使用子查询连接，我还尝试过分组和按 asc 排序。我没主意了。

预期输出：如果从 TABLE1 中选择，则匹配前两个整数，如果匹配，则比较地址。如果匹配，则不显示。如果没有匹配，则显示不匹配的地址。反之亦然，如果从 TABLE2 中选择，则匹配前两个整数。如果匹配，则在地址上比较，如果不匹配，则显示应为空(无数据(。

任何帮助将不胜感激。

谢谢。

Answer 1

此查询：

SELECT A.ADDRESS, A.INTER1, A.INTER2
FROM TABLE1 A LEFT JOIN
     TABLE2 B
     ON A.INTER1 = B.INTER1 AND
        A.INTER2 = B.INTER2 AND
        A.ADDRESS <> B.ADDRESS;

将返回 A 中的所有行。 这就是LEFT JOIN所做的。 这是它应该做的。

如果要筛选第一个表中的行，请更改JOIN类型：

SELECT A.ADDRESS, A.INTER1, A.INTER2
FROM TABLE1 A INNER JOIN
     TABLE2 B
     ON A.INTER1 = B.INTER1 AND
        A.INTER2 = B.INTER2 AND
        A.ADDRESS <> B.ADDRESS;

然后，还可以将不等式条件移动到 WHERE 。 但是使用LEFT JOIN然后将不等式移动到WHERE是相当荒谬的 - 为什么要指定一个外部连接，然后在WHERE子句中撤消它？

Answer 2

试试这个：

SELECT 
        ifnull(f1.INTER1, f2.INTER1) as INTER1, 
        ifnull(f1.INTER2, f2.INTER2) as INTER2, 
        f1.ADDRESS as ADDRESS_A, f2.ADDRESS as ADDRESS_B,
        case 
        when f1.INTER1 then 'ADDRESS NOT IN TABLE1'
        when f2.INTER1 then 'ADDRESS NOT IN TABLE2'
        else 'ADDRESS ARE DIFFERENT' end as DIAGNOSTIC
FROM TABLE1 f1 
FULL OUTER JOIN TABLE2 f2 ON (f1.INTER1, f1.INTER2)  = (f2.INTER1, f2.INTER2)
where 
        f1.INTER1 is null or 
        f2.INTER1 is null or 
        f1.ADDRESS <> f2.ADDRESS;

Answer 3

--other solution if you want your result into multiple rows
select * from 
(
select ADDRESS, INTER1, INTER2 from table1
except
select ADDRESS, INTER1, INTER2 from table2
) tmp1
union all 
select * from 
(
select ADDRESS, INTER1, INTER2 from table2
except
select ADDRESS, INTER1, INTER2 from table1
) tmp2

Answer 4

此问题的解决方案如下：

SELECT 
     A.ADDRESS
   , A.INTER1
   , A.INTER2
FROM TABLE1 AS A
    INNER JOIN TABLE2 AS B
       ON A.INTER1 = B.INTER1
       AND A.INTER2 = B.INTER2
    LEFT JOIN TABLE2 AS B1
       ON B1.ADDRESS <> B.ADDRESS
WHERE A.ADDRESS <> B.ADDRESS
AND B1.ADDRESS IS NOT NULL

这允许匹配前两个整数值，然后在地址上连接。但是，这会导致具有多个地址的记录仍显示。这被 WHERE 子句比较所消除。

这是提供程序数据的实际生产问题。一旦找到了解决方案，它看起来非常简单 - 但到达解决方案总是棘手的部分，对吧？我希望这对其他人有所帮助。

编辑 04/24/2016

经过更多的质量测试，这个解决方案需要额外的条款，即：

SELECT 
     A.ADDRESS
   , A.INTER1
   , A.INTER2
FROM TABLE1 AS A
    INNER JOIN TABLE2 AS B
       ON A.INTER1 = B.INTER1
       AND A.INTER2 = B.INTER2
    LEFT JOIN TABLE2 AS B1
       ON B1.ADDRESS <> B.ADDRESS
WHERE NOT IN (SELECT B2.ADDRESS FROM TABLE2 AS B2 
                   INNER JOIN TABLE1 AS A2 
                     ON B2.INTER1 = A2.INTER1
                      AND B2.INTER2 = A2.INTER2)
 A.ADDRESS <> B.ADDRESS
AND B1.ADDRESS IS NOT NULL

当然，"WHERE NOT IN"允许子查询排除找到的任何内容。这也可以使用 CTE 来完成，然后在 NOT IN 子句中排除。我希望这对其他人有所帮助。