I have two tables, that look something like this:

Table A

time_unix   time_msecs   measurementA
1000        329          3.14159
1000        791          9.32821
1001        227          138.3819
1001        599          -15.3289

Table B

time_unix   time_msecs   measurementB
1000        565          17.2938
1000        791          12348.132
1001        227          -128.3283
1001        293          225.12938

For both tables, I'm using a composite key (made up of time_unix, time_msecs). These measurement data (measurementA and measurementB) are in two different tables, because there are actually many, many columns (many thousands) - too many to keep in a single table.

I want to perform a query such that the result set is simply my keys and a select few columns combined from these two tables. Sometimes the times (keys) line up, sometimes they don't. If they don't, I just would like a null value returned for that column.

Desired Result

time_unix   time_msecs   measurementA    measurementB
1000        329          3.14159         (null)
1000        565          (null)          17.2938
1000        791          9.32821         12348.132
1001        227          138.3819        -128.3283
1001        293          (null)          225.12938
1001        599          -15.3289        (null)

How to achieve this? I don't think JOINs are the way to go. I have otherwise been combining datasets inside Javascript, and it seems terribly cumbersome. There must be a way to do this on the database side.

CodePudding user response：

You want a full outer join between the two tables:

SELECT a.time_unix,
       a.time_msecs,
       a.measurementA,
       b.measurementB
FROM TableA a
LEFT JOIN TableB b
    ON a.time_unix = b.time_unix AND
       a.time_msecs = b.time_msecs
UNION ALL
SELECT b.time_unix,
       b.time_msecs,
       a.measurementA,
       b.measurementB
FROM TableA a
RIGHT JOIN TableB b
    ON a.time_unix = b.time_unix AND
       a.time_msecs = b.time_msecs
WHERE a.time_unix IS NULL
ORDER BY 1, 2;

CodePudding user response：

I used union since there are no full joins in MySQL.

select TableA.time_unix
      ,TableA.time_msecs
      ,TableA.measurementA
      ,TableB.measurementB
from TableA
left join TableB on TableA.time_msecs = TableB.time_msecs and
                    TableA.time_unix  = TableB.time_unix
union
select TableB.time_unix
      ,TableB.time_msecs
      ,TableA.measurementA
      ,TableB.measurementB 
FROM TableA
right join TableB on TableA.time_msecs = TableB.time_msecs and
                     TableA.time_unix  = TableB.time_unix
order by time_unix

Fiddle