CODEWITHSUNDEEP
sqlsql-serverjoinfull-outer-join
mattalxndr
mattalxndr

Reputation: 9418

How can I do a full outer join on possibly null keys?

How do accomplish this full outer join?

Note: I am asking a different question than in my original post.

TABLE1

+----+-----------+-----------+---------+
| ID | FILTER1   | FILTER2   | DATA1   |
| 1  | NULL      | filter2-A | data1-A |
| 2  | filter1-B | filter2-B | data1-B |
| 3  | filter1-C | filter2-C | data1-C |
| 3  | filter1-D | filter2-D | data1-D |
+----+-----------+-----------+---------+

TABLE2

+----+-----------+-----------+---------+
| ID | FILTER1   | FILTER2   | DATA2   |
| 1  | filter1-A | filter2-A | data2-A |
| 2  | filter1-B | filter2-B | data2-B |
| 3  | filter1-C | filter2-C | data2-C |
| 3  | filter1-D | filter2-D | data2-D |
+----+-----------+-----------+---------+

Result

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

Some concerns:

The two tables actually share five filter fields and I have to do some filtering on the result, which means I need to refer to the filter fields a lot. And I don't want a bunch of calls to the very verbose COALESCE() or ISNULL() everywhere. Any way to avoid that without resorting to a subquery?

Upvotes: 0

Views: 805

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I'll re-iterate my answer to that question.

If you don't have duplicate key value (pairs) within a table (as in your sample data), then you can do this using union all and group by:

select filter_1, filter_2, max(data_1) as data_1, max(data_2) as data_2
from ((select filter_1, filter_2, data_1, NULL as data_2
       from table1
      ) union all
      (select filter_1, filter_2, NULL, data_2
       from table2
      )
     ) t
group by filter_1, filter_2;

Upvotes: 2

Related Questions