如何在SQL中完成此联接? [英] How do accomplish this join in SQL?

问题描述

如何在SQL中完成此联接?

How do accomplish this join in SQL?

TABLE1

+----+-----------+-----------+---------+
| ID | FILTER1   | FILTER2   | DATA1   |
| 1  | filter1-A | filter2-A | data1-A |
| 2  | filter1-B | filter2-B | data1-B |
+----+-----------+-----------+---------+

TABLE2

+----+-----------+-----------+---------+
| ID | FILTER1   | FILTER2   | DATA1   |
| 1  | filter1-B | filter2-B | data2-B |
| 2  | filter1-C | filter2-C | data2-C |
+----+-----------+-----------+---------+

结果

+------------+-----------+---------+---------+
| FILTER1    | FILTER2   | DATA1   | DATA2   | 
| filter1-A  | filter2-A | data1-A | NULL    | 
| filter1-B  | filter2-B | data1-B | data2-B | 
| filter1-C  | filter2-C | NULL    | data2-C | 
+------------+-----------+---------+---------+

推荐答案

SELECT
  COALESCE(TABLE1.filter_1, TABLE2.filter_1)   AS filter_1,
  COALESCE(TABLE1.filter_1, TABLE2.filter_2)   AS filter_2,
  TABLE1.data1                                 AS data_1,
  TABLE2.data2                                 AS data_2
FROM
  TABLE1
FULL OUTER JOIN
  TABLE2
    ON  TABLE1.filter_1 = TABLE2.filter_1
    AND TABLE1.filter_2 = TABLE2.filter_2

FULL OUTER JOIN保留每个表中的每个记录，而不管另一个表中是否有匹配项.

The FULL OUTER JOIN keeps every record from each table, regardless of whether or not there is a match in the other table.

COALESCE() (某些用途ISNULL())然后可用于扫描缺失/NULL值以找到第一个非NULL值.

The COALESCE() (Some use ISNULL()) then can be used to scan through missing/NULL values to find the first non-NULL value.

这篇关于如何在SQL中完成此联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！