0

I am using code:

 create table table3 as
    select a.*,b.* 
    from
    table1 a
    left join
    table2 b
    on a.id=b.id
    where a.date>=b.date
    and a.age<b.age

however,table1 has 20000 rows and table3 has only 5000 rows.It seems the where clause filter all the null values.

When I am using code：

create table table4 as
        select a.*,b.* 
        from
        table1 a
        left join
        table2 b
        on (a.id=b.id
        and a.date>=b.date
        and a.age<b.age)

I would get an error "both left and right aliases encountered in join",because I used inequality in join conditions.

So,how could I get more than 20000 rows include null values by using left join?Should I join multiple times or could use another more effective way?

CD_Lee
  • 99
  • 1
  • 13
  • https://stackoverflow.com/questions/39852325/both-left-and-right-aliases-encountered-in-hive-join-without-any-inequality-cla – Bugbeeb May 15 '19 at 23:32
  • @Bugbeeb Yes,I have noticed this post,but the where clause will filter all the null values.how to keep the null values with out this inequality error? – CD_Lee May 15 '19 at 23:34
  • @CD_Lee have you ever gotten this problem resolved? I am struggling with the exact same problem now. – bernando_vialli Dec 01 '19 at 14:46

0 Answers0