Making a query to merge two tables upon criteria, with giving priority to the rows from the second table

Question

I’ve got two MS Access tables.

table1
ID  Country     Dat         Val
1   Australia   01.10.2021  10
2   Canada      01.10.2021  100
3   Japan       01.10.2021  1000
4   Australia   02.10.2021  20
5   Canada      02.10.2021  200
6   Japan       02.10.2021  2000

and

table2
ID  Country     Dat         Val
1   Canada      01.10.2021  50000
2   Greece      01.10.2021  50100
3   Canada      02.10.2021  60000
4   Greece      02.10.2021  60100

This SQL-code allows me to merge two tables, which gives 10 rows.

   SELECT table2.Dat,
          table2.Country,
          table2.Val
     FROM table2
LEFT JOIN table1
       ON (table1.Dat = table2.Dat)
      AND (table1.Country = table2.Country)

UNION ALL

   SELECT table1.Dat,
          table1.Country,
          table1.Val
     FROM table1
LEFT JOIN table2
       ON (table1.Dat = table2.Dat)
      AND (table1.Country = table2.Country)

 ORDER BY table2.Dat,
          table2.Country;

And it gives duplicates based on "Date" and "Country" criteria.

The question

I wonder, what should the code look like if I would like to merge two tables in a way that, if table1 and table2 have the same "Date" and "Country", then table2 rows would be chosen, not both?

In other words, how to take all unique rows from table1 and table2 that doesn’t match criteria "Date" AND "Country", as well as give precedence to table2 when rows in two tables match the criteria "Date" AND "Country"?

(A "Date + Country" bundle is unique within each table, i.e. there is only one "Data + Country" within one table)

In other words, what query could I use to make the result look like this, i.e. 8 rows without date + country duplicates?

Expected Result
Dat         Country     Val
01.10.2021  Australia   10
01.10.2021  Canada      50000
01.10.2021  Greece      50100
01.10.2021  Japan       1000
02.10.2021  Australia   20
02.10.2021  Canada      60000
02.10.2021  Greece      60100
02.10.2021  Japan       2000

And what might be a good way to make such SQL-scripts to merge tables based upon several criteria like this?

Answer 1

All you need is UNION ALL for the 2 tables and use NOT EXISTS in the WHERE clause for table1:

SELECT t1.Dat, t1.Country,  t1.Val FROM table1 AS t1
WHERE NOT EXISTS (SELECT 1 FROM table2 AS t2 WHERE t2.Country = t1.Country AND t2.Dat = t1.Dat)
UNION ALL
SELECT Dat, Country, Val FROM table2
ORDER BY Dat, Country

Results:

Dat         Country     Val
1/10/2021   Australia   10
1/10/2021   Canada      50000
1/10/2021   Greece      50100
1/10/2021   japan       1000
2/10/2021   Australia   20
2/10/2021   Canada      60000
2/10/2021   Greece      60100
2/10/2021   Japan       2000

Answer 2

The basic way is just to split such tasks into sub-tasks.

The smaller, thus easier, sub-tasks here could be the following:

1. Get all rows (matching the criteria "Date" and "Country") which exist in table2, but are absent in table1.
2. Get all rows (matching the criteria "Date" and "Country") which exist in table1, but are absent in table2.
3. Get all rows (matching the criteria "Date" and "Country") which exist in both table2 and table1, but taking rows only from table2.
4. Put UNION ALL in between the parts you’ve previously created and tested (to make sure everything works properly).

Therefore, here is a possible strightforward solution:

   SELECT table2.Dat,
          table2.Country,
          table2.Val
     FROM table2
LEFT JOIN table1
       ON (table1.Country = table2.Country)
      AND (table1.Dat = table2.Dat)
    WHERE table1.Country IS NULL
      AND "comment: Getting from table2 everything that is absent in table1 (matching country and date criteria)"

UNION ALL

   SELECT table1.Dat,
          table1.Country,
          table1.Val
     FROM table1
LEFT JOIN table2
       ON (table1.Country = table2.Country)
      AND (table1.Dat = table2.Dat)
    WHERE table2.Country IS NULL
      AND "comment: Getting from table1 everything that is absent in table2 (matching country and date criteria)"

UNION ALL

   SELECT table2.Dat,
          table2.Country,
          table2.Val
     FROM table2,
          table1
    WHERE table1.Country = table2.Country
      AND table1.Dat = table2.Dat
      AND "comment: getting from table2 everything that exists in table1 (matching country and date criteria)"

 ORDER BY table2.Dat,
          table2.Country;

Conclusion

So the problem here is just to take the corresponding rows from "overlapping sets", and then merging them.

To undestand a bit more, you might have a look at Venn diagram and some other answers here (SO) or non-Venn way explanation here.