You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jaime Solano <jd...@gmail.com> on 2015/03/25 14:29:44 UTC
Non-equi joins
Hi guys,
I'm trying to figure out a way to join two tables with non-equi conditions.
Basically, something like:
SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
Is this feature supported in version 4.3? If not, is there a workaround to
make it work in 4.2.0?
Thanks in advance!
-Jaime
Re: Non-equi joins
Posted by Maryann Xue <ma...@gmail.com>.
No. Both the cross join and the post filter are done on the server side if
a hash join is possible, otherwise on the client side by a sort merge join.
Thanks,
Maryann
On Wed, Mar 25, 2015 at 2:27 PM, Abe Weinograd <ab...@flonet.com> wrote:
> Is this all applied on the client?
>
> Thanks,
> Abe
>
> On Wed, Mar 25, 2015 at 1:59 PM, Maryann Xue <ma...@gmail.com>
> wrote:
>
>> Yes, 4.3 only.
>>
>> On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano <jd...@gmail.com>
>> wrote:
>>
>>> Thanks for your response, Maryann!
>>>
>>> Again, this suggestion is for 4.3 only, right?
>>> On Mar 25, 2015 12:34 PM, "Maryann Xue" <ma...@gmail.com> wrote:
>>>
>>>> Actually we do in 4.3, but apparently not in an efficient way. If no
>>>> equi conditions are specified, Phoenix simply does cross join and applies a
>>>> post filter to the cross joined results. And we do not support non-equi
>>>> conditions in ON clause, so non-equi outer join is currently impossible.
>>>> But if you are doing inner joins, you can adjust your query as:
>>>>
>>>> SELECT * FROM T1, T2 WHERE T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
>>>>
>>>>
>>>> On Wed, Mar 25, 2015 at 9:29 AM, Jaime Solano <jd...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi guys,
>>>>> I'm trying to figure out a way to join two tables with non-equi
>>>>> conditions. Basically, something like:
>>>>> SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <=
>>>>> T2.COL3;
>>>>> I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
>>>>> Is this feature supported in version 4.3? If not, is there a
>>>>> workaround to make it work in 4.2.0?
>>>>> Thanks in advance!
>>>>> -Jaime
>>>>>
>>>>
>>>>
>>
>
Re: Non-equi joins
Posted by Abe Weinograd <ab...@flonet.com>.
Is this all applied on the client?
Thanks,
Abe
On Wed, Mar 25, 2015 at 1:59 PM, Maryann Xue <ma...@gmail.com> wrote:
> Yes, 4.3 only.
>
> On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano <jd...@gmail.com> wrote:
>
>> Thanks for your response, Maryann!
>>
>> Again, this suggestion is for 4.3 only, right?
>> On Mar 25, 2015 12:34 PM, "Maryann Xue" <ma...@gmail.com> wrote:
>>
>>> Actually we do in 4.3, but apparently not in an efficient way. If no
>>> equi conditions are specified, Phoenix simply does cross join and applies a
>>> post filter to the cross joined results. And we do not support non-equi
>>> conditions in ON clause, so non-equi outer join is currently impossible.
>>> But if you are doing inner joins, you can adjust your query as:
>>>
>>> SELECT * FROM T1, T2 WHERE T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
>>>
>>>
>>> On Wed, Mar 25, 2015 at 9:29 AM, Jaime Solano <jd...@gmail.com>
>>> wrote:
>>>
>>>> Hi guys,
>>>> I'm trying to figure out a way to join two tables with non-equi
>>>> conditions. Basically, something like:
>>>> SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <=
>>>> T2.COL3;
>>>> I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
>>>> Is this feature supported in version 4.3? If not, is there a workaround
>>>> to make it work in 4.2.0?
>>>> Thanks in advance!
>>>> -Jaime
>>>>
>>>
>>>
>
Re: Non-equi joins
Posted by Maryann Xue <ma...@gmail.com>.
Yes, 4.3 only.
On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano <jd...@gmail.com> wrote:
> Thanks for your response, Maryann!
>
> Again, this suggestion is for 4.3 only, right?
> On Mar 25, 2015 12:34 PM, "Maryann Xue" <ma...@gmail.com> wrote:
>
>> Actually we do in 4.3, but apparently not in an efficient way. If no equi
>> conditions are specified, Phoenix simply does cross join and applies a post
>> filter to the cross joined results. And we do not support non-equi
>> conditions in ON clause, so non-equi outer join is currently impossible.
>> But if you are doing inner joins, you can adjust your query as:
>>
>> SELECT * FROM T1, T2 WHERE T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
>>
>>
>> On Wed, Mar 25, 2015 at 9:29 AM, Jaime Solano <jd...@gmail.com>
>> wrote:
>>
>>> Hi guys,
>>> I'm trying to figure out a way to join two tables with non-equi
>>> conditions. Basically, something like:
>>> SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <=
>>> T2.COL3;
>>> I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
>>> Is this feature supported in version 4.3? If not, is there a workaround
>>> to make it work in 4.2.0?
>>> Thanks in advance!
>>> -Jaime
>>>
>>
>>
Re: Non-equi joins
Posted by Jaime Solano <jd...@gmail.com>.
Thanks for your response, Maryann!
Again, this suggestion is for 4.3 only, right?
On Mar 25, 2015 12:34 PM, "Maryann Xue" <ma...@gmail.com> wrote:
> Actually we do in 4.3, but apparently not in an efficient way. If no equi
> conditions are specified, Phoenix simply does cross join and applies a post
> filter to the cross joined results. And we do not support non-equi
> conditions in ON clause, so non-equi outer join is currently impossible.
> But if you are doing inner joins, you can adjust your query as:
>
> SELECT * FROM T1, T2 WHERE T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
>
>
> On Wed, Mar 25, 2015 at 9:29 AM, Jaime Solano <jd...@gmail.com> wrote:
>
>> Hi guys,
>> I'm trying to figure out a way to join two tables with non-equi
>> conditions. Basically, something like:
>> SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <=
>> T2.COL3;
>> I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
>> Is this feature supported in version 4.3? If not, is there a workaround
>> to make it work in 4.2.0?
>> Thanks in advance!
>> -Jaime
>>
>
>
Re: Non-equi joins
Posted by Maryann Xue <ma...@gmail.com>.
Actually we do in 4.3, but apparently not in an efficient way. If no equi
conditions are specified, Phoenix simply does cross join and applies a post
filter to the cross joined results. And we do not support non-equi
conditions in ON clause, so non-equi outer join is currently impossible.
But if you are doing inner joins, you can adjust your query as:
SELECT * FROM T1, T2 WHERE T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
On Wed, Mar 25, 2015 at 9:29 AM, Jaime Solano <jd...@gmail.com> wrote:
> Hi guys,
> I'm trying to figure out a way to join two tables with non-equi
> conditions. Basically, something like:
> SELECT * FROM T1 LEFT JOIN T2 ON T1.COL1 >= T2.COL2 AND T1.COL1 <= T2.COL3;
> I'm using Phoenix 4.2.0, which doesn't support non-equi joins.
> Is this feature supported in version 4.3? If not, is there a workaround to
> make it work in 4.2.0?
> Thanks in advance!
> -Jaime
>