How do I merge two partially overlapping lists in MySQL?

Question

OK, so I have two tables (views actually) that both have the same structure. Each has a column with a date and a column with a numerical value.

The dates in both tables will be almost continuous and cover the same period. The data will consequently be largely the same, but a date in one table may not appear in the other.

I want to have one table, with one date column and two numerical columns. Where there is no date in one table, the corresponding numerical field should be null.

Any approaches out there?

---

PS This is not the same question: http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables

Answer 1

This query is a union of 3 queries: The first joins the records that are common in both tables. The second adds the ones that only exist in table1. The third adds the records that are only in table2.

SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1, table2 as t2
WHERE t1.dt = t2.dt
UNION
SELECT t1.td, t1.val as val1, null as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
WHERE t2.td IS NULL
UNION
SELECT t2.td, null as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL

Answer 2

VladiatOr's solution can be shortened by having the first part select records present in both and only in the first table and then adding those only present in the second table.

SELECT t1.td, t1.val as val1, t2.val as val2
  FROM table1 as t1
  LEFT JOIN table2 as t2
  ON t1.td = t2.td
UNION
SELECT t2.td, t1.val as val1, t2.val as val2
  FROM table2 as t2
  LEFT JOIN table1 as t1
  ON t2.td = t1.td
WHERE t1.td IS NULL

See also the comment from Tobias Riemenschneider in the MySQL Reference on JOIN Syntax.