Title
r
Ryan Hamilton
01/31/2023, 3:24 PMHi,
Hopefully a quick question.
I have: queryA - returns (ts timestamp, price, ..........)
queryB - returns (ts timestamp, size, ...........)
I want a combined result (ts, price, size, ........)
Where ts is matching join, where ts only occurs in queryB append as a new row.
In postgres I believe this may be merge but I can't find it in your help docs.
Is this possible in questDB?
j
javier ramirez
01/31/2023, 4:07 PMNot sure if you after a left outer join? https://questdb.io/docs/reference/sql/join/#left-outer-join
In that case, matching rows would have a combined results, but TSs appearing only on the left table would get a row with only the columns from that table
r
Ryan Hamilton
01/31/2023, 4:23 PMClose but I also want the rows that only occurred on the right.
Full-outer join it looks like
Not listed on your joins page so I guess not supported?
j
javier ramirez
01/31/2023, 4:57 PMNot supported right now. My best guess would be to do two left outer JOINS (with table A on the left first and then table B on the left) and do an EXCEPT to remove the duplicated rows, but I am not sure if it is the best approach and I just pinged the core team at our internal slack to see if they have a better idea
r
Ryan Hamilton
01/31/2023, 4:59 PMAs a workaround for now, I'm using UNION and the downstream app mostly ignores the fact the rows are not shown as one.
But the workaround highlights that I would also want "fills" options. i.e. How to fill the gaps of an outer join.
But this issue is no longer a blocker.
Thanks for your help.
FYI loved your non-conventional solution 🙂
j
javier ramirez
01/31/2023, 5:01 PMhahaha.. nothing like constraints to have to think outside the box
b
Bolek Ziobrowski
01/31/2023, 5:08 PMWhat Javier proposed would look like :
select a.*,b.* from a left join b on X --A + A&B
union all
(
select a.*,b.* from b left join a on X --B+A&B
except
select a.*,b.* from b join a on X -- A&B
)select a.*,b.* from a left join b on X --A + A&B
union all
select * from
(
select a.*,b.* --B
from b left join a on X)
where a.ts is null -- any non-nullable column
)m
Matt
01/31/2023, 7:47 PMAnother way to think about it is that you need 2 outer joins, and you need to avoid duplicates. Phrased like that, you realize that you have these solutions:
• 2 left joins, remove duplicates with EXCEPT (Javier suggestion)
• 2 left joins, prevent duplicates with filter (Bolek suggestion)
• 2 left joins, remove duplicates with UNION
• Full outer join as syntactic sugar (not available)
/* CTE to make example self-contained */
with price_stuff as (
select '2019-10-17T00:00:00.000000Z'::timestamp as ts, 'price_1' as price
union all select '2019-10-17T00:00:00.400000Z'::timestamp, 'price_2'
union all select '2019-10-17T00:00:03.000000Z'::timestamp, 'price_3'
),
size_stuff as (
select '2019-10-17T00:00:00.000000Z'::timestamp as ts, 'size_1' as size
union all select '2019-10-17T00:00:02.400000Z'::timestamp, 'size_4'
)
/* In general, this filter is a useful pattern */
select p.ts, p.price, s.size
from
price_stuff p
left outer join size_stuff s on ( p.ts = s.ts )
union all
select s.ts, p.price, s.size
from
size_stuff s
left outer join price_stuff p on ( p.ts = s.ts )
where p.ts is null -- filter out duplicates
/* In this case, I think Union would be identical */
select p.ts, p.price, s.size
from
price_stuff p
left outer join size_stuff s on ( p.ts = s.ts )
union -- not ALL
select s.ts, p.price, s.size
from
size_stuff s
left outer join price_stuff p on ( p.ts = s.ts )r
Ryan Hamilton
01/31/2023, 7:58 PMThanks to both of you, I will give it a try and let you know. Hopefully I'll even get to show you the end product.
b
Bolek Ziobrowski
02/01/2023, 8:13 AMProper full outer join implementation would likely to be much faster than these workarounds so if you need it please create an issue on Git Hub .
m
Miguel Arregui
02/01/2023, 8:19 AMI have created an entry for documentation, to keep track of this thread https://github.com/questdb/questdb.io/issues/1363