How about: select a.pid, b.pid tab1 a left join tab2 b on (a.pid=b.pid); union all -- Need 'union all' to remove dups for the case of when (a.pid=b.pid) and (substr(a.pid,1,27)=b.pid) select a.pid, b.pid tab1 a left join tab2 b on (substr(a.pid,1,27)=b.pid);
--- Mark E. Sunderlin Solutions Architect |AOL Core Data Technologies P: 703-265-6935 |C: 540-327-6222 | AIM: MESunderlin 22000 AOL Way | Dulles, VA | 20166 On Thu, Jul 26, 2012 at 5:11 PM, 周彩钦 <caiqinz...@gmail.com> wrote: Hi all, I have problem when using left join with hive 0.7.1. I have a query below: select a.pid, b.pid tab1 a left join tab2 b on (a.pid=b.pid or substr(a.pid,1,27)=b.pid); But hive don't support "OR" in left join. Table a is huge, and table b has 40000 rows now(will increase). Is there any other solution to achieve this? Thanks very much. -- -- Bertrand Dechoux -- /**********************************************************/ // 姓名：周彩钦 // 联系电话：15210364513 // E-mail：caiqinz...@gmail.com ／**********************************************************/ -- Bertrand Dechoux -- /**********************************************************/ // 姓名：周彩钦 // 联系电话：15210364513 // E-mail：caiqinz...@gmail.com ／**********************************************************/
