CODEWITHSUNDEEP
sqljoinhiveleft-joinhiveql
user2438292
user2438292

Reputation: 107

Why Hive SQL returning NULL values for a particular column in Select statement when that column has all double values?

I'm using Hive SQL. Version is Hive 1.1.0-cdh5.14.0. In my example below, sp.close is a column with type double values. I checked sp.column and there are definitely no NULL values. Yet, in this select statement below, sp.close shows all NULL values. Why?

select
    step1.*,
    sp.close

from
    step1 left join stockprices2 sp on (
        step1.symbol = sp.symbol and
        step1.year = sp.year and
        step1.startmonth = sp.month and
        step1.startday = sp.day and
        step1.sector = sp.sector
    )

;

Upvotes: 0

Views: 819

Answers (1)

GMB
GMB

Reputation: 222402

Most likely, your left join did not find a matchin row in stockprices2. In that event, the row from step1 is retained, but all columns from stockprices2 will be null in the resultset. This is by design how the database signals that the left join came up empty.

You can easily verify that by just chaning the left join to an inner join: you should have less rows returned (where there is no match in stockprices2, the row from step1 is removed from the resultset), and no null values in sp.close.

Or you can add one of the columns used in the left join conditions in the select clause, and see that it's null too.

select
    st.*,
    sp.close,
    sp.symbol   -- null too
from step1 st 
left join stockprices2 sp 
    on  st.symbol = sp.symbol 
    and st.year = sp.year 
    and st.startmonth = sp.month 
    and st.startday = sp.day 
    and st.sector = sp.sector

Side note: the parentheses around the join conditions are superfluous.

Upvotes: 1

Related Questions