sql - How to join tables selecting both matched and unmatched records?

Question

I have tables that do a count of a certain action for different days. I am trying to join those tables to show each type of action and the count for each day. If the action happened on both days its fine. But if I have an action that only happened on day one or only on day two, I would like those as well.

I have tried different types of joins and unions and can not seem to get it to show the expect results.

In table 1 and 2 I have first four columns that match based on the pl & sta(the two fields I want to join on) so I should get the count from both tables as shown in the outcome. However table 1 has one that is 414 - 1PL with a count of 26 and table 2 has 41M - 2PL with a count of 12. I would like each of those that are unmatching returned in the pl and sta fields with the count of each one under that column for that days count.

Table data:

Table 1
pl  | sta | count_1
----+-----+--------
410 | 1PL |    7777
410 | MPL |      31
412 | 1PL |      67
412 | 3PL |       1
414 | 1PL |      26

Table 2
pl  | sta | count_2
----+-----+--------
410 | 1PL |    7000
410 | MPL |      26
412 | 1PL |      52
412 | 3PL |       1
41M | 2PL |      12

Outcome
pl  | sta | count_1 | count_2
----+-----+---------+--------
410 | 1PL |    7777 |    7000
410 | MPL |      31 |      26
412 | 1PL |      67 |      52
412 | 3PL |       1 |       1
414 | 1PL |      26 |       0
41M | 2PL |       0 |      12

标签： sqljoin

Answer 1

I think you need a full join and coalesce:

SELECT COALESCE(t1.pl, t2.pl) AS pl
    , COALESCE(t1.sta, t2.sta) AS sta
    , COALESCE(t1.count_1, 0) AS count_1
    , COALESCE(t2.count_2, 0) AS count_2
FROM t1
FULL JOIN t2 ON t1.pl = t2.pl AND COALESCE(t1.sta, -99) = COALESCE(t2.sta, -99)

In order to account for cases where sta is NULL, replace sta with -99 or some other value that is otherwise invalid.