SQL query – Do I need a full join to produce this combined result

amazon-prestojoin;query

With two tables structured like this:

Table1                       Table2
----------------             ----------------
id, time, x                  id, time, y, z
1   1     1                  1   2     2  2
1   2     2                  1   3     3  3

I am trying to join the tables to get a result that looks like this:

Result
--------------------------------
id, time, x, y, z
1   1     1  
1   2     2  2  2
1   3        3  3

I was able to get a partial result with a query like this, but it is returning all of the values from table2 (including undesired ids) along with the joined values of table1.

select * 
from Table1 
full outer join Table2 on Table1.time=Table2.time and Table1.id ='1' and Table2.id ='1'
order by Table2.time

Does anyone know how to fix? I'm trying to run this query in AWS Athena/Prestodb if that matters.

Thanks for the help!

Best Answer

I don't use amazon-presto, but I'm wondering if you could adapt this SQL Server solution to your needs.

--demo setup
drop table if exists table1;
drop table if exists table2;
CREATE TABLE Table1
    (id int, time int, x int)
;

INSERT INTO Table1
    (id, time, x)
VALUES
    (1, 1, 1),
    (1, 2, 2)
;


CREATE TABLE Table2
    (id int, time int, y int, z int)
;

INSERT INTO Table2
    (id, time, y, z)
VALUES
    (1, 2, 2, 2),
    (1, 3, 3, 3)
;

------------------
--solution
;with cte as
(
select id, time from Table1
union
select id, time from Table2
)
SELECT c.id, c.TIME, t1.x, t2.y, t2.z
FROM cte c
LEFT JOIN table1 t1
    ON t1.id = c.id
        AND t1.TIME = c.TIME
LEFT JOIN table2 t2
    ON t2.id = c.id
        AND t2.TIME = c.TIME;

| id | TIME | x    | y    | z    |
|----|------|------|------|------|
| 1  | 1    | 1    | NULL | NULL |
| 1  | 2    | 2    | 2    | 2    |
| 1  | 3    | NULL | 3    | 3    |
Related Question