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
Thanks for your response, Maryann! Again, this suggestion is for 4.3 only, right? On Mar 25, 2015 12:34 PM, Maryann Xue maryann@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 jdjsol...@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
Yes, 4.3 only. On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano jdjsol...@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 maryann@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 jdjsol...@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
Is this all applied on the client? Thanks, Abe On Wed, Mar 25, 2015 at 1:59 PM, Maryann Xue maryann@gmail.com wrote: Yes, 4.3 only. On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano jdjsol...@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 maryann@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 jdjsol...@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
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 a...@flonet.com wrote: Is this all applied on the client? Thanks, Abe On Wed, Mar 25, 2015 at 1:59 PM, Maryann Xue maryann@gmail.com wrote: Yes, 4.3 only. On Wed, Mar 25, 2015 at 1:25 PM, Jaime Solano jdjsol...@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 maryann@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 jdjsol...@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
