CODEWITHSUNDEEP
sqlpostgresqljoinselectfull-outer-join
saintlyzero
saintlyzero

Reputation: 1842

Merge output of different tables

I have two tables t1 and t2. I need to merge their output
t1

 day_hour | col1 
----------+------
 09 AM    |    1
 10 AM    |    3
 11 AM    |    0
 12 PM    |    4

t2

 day_hour | col2 
----------+------
 08 AM    |    11
 11 AM    |    7
 12 PM    |    7
 01 PM    |    6
 02 PM    |    4

The output should be

 day_hour |   col1  |  col2 
----------+---------+--------
 08 AM    |    NULL |    11
 09 AM    |    1    |    NULL
 10 AM    |    3    |    NULL
 11 AM    |    0    |    7
 12 PM    |    4    |    7
 01 PM    |    NULL |    6
 02 PM    |    NULL |    4

So far I have tried FULL OUTER JOIN, but that does not give me the merged day_hour
Here's my query 

SELECT t1.day_hour, t1.col1, t2.col2
FROM (SELECT day_hour, col1 FROM t1)
FULL OUTER JOIN 
(SELECT day_hour, col1 FROM t2)
ON t1.day_hour = t2.day_hour;

I'm using postgres 11

Upvotes: 0

Views: 47

Answers (3)

Andrew
Andrew

Reputation: 8693

I think you want to coalesce day_hour from the two tables.

select
coalesce(t1.day_hour,t2.day_hour),
t1.col1,
t2.col1,
...

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use an UNION 

    select t.day_hour , t1.col1, t2.col2
    from (
        select day_hour
        from  t1
        union 
        select day_hour
        from  t2

        ) t1
        left join t1 on t1.day_hour = t.day_hour
        left join  t2 on t2.day_hour = t.day_hour

Upvotes: 2

GMB
GMB

Reputation: 222402

You can use COALESCE():

SELECT COALESCE(t1.day_hour, t2.day_hour) day_hour, t1.col1, t2.col2
FROM t1
FULL OUTER JOIN t2 ON t1.day_hour = t2.day_hour;

But in Postgres the USING join clause comes handy for this:

SELECT day_hour, t1.col1, t2.col2
FROM t1
FULL OUTER JOIN t2 USING(day_hour)

Note that the subqueries are unnecessary - I remove them.

Upvotes: 2

Related Questions