Hi Furcy, Thanks for your reply. I could make a test data by using OUTER JOIN query.
Best regards, Jinchul 2017-11-29 17:23 GMT+09:00 Furcy Pin <pin.fu...@gmail.com>: > Hello Jinchul, > > in SQL, any type (even complex types such as structs) can be nulls. > And this happen as often as the use of (LEFT | RIGHT | FULL) JOINS: > when a record is not matched by the joined table, it will be NULL. > > As far as I remember, directly creating NULL with complex types (for > testing purpose) is complicated in Hive > because of type checking: you can cast a NULL into a primary type but it > is not that easy to obtain a complex type. > > Finally, about the meaning of NULL: it helped me a lot when I realized > that in SQL, a NULL should be interpreted as a "We don't know" > or "This value is missing and could be anything": this is why TRUE, FALSE, > and NULL implement the three-valued logic, as explained here: > > https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_ > NULL_and_the_three-valued_logic_.283VL.29 > > > Regards, > > Furcy > > > > > 2017-11-29 1:18 GMT+01:00 Jin Chul Kim <jinc...@gmail.com>: > >> Hi, >> >> May I know the meaning of IS [NOT] NULL for a complex type such as >> STRUCT? >> As far as I know, we cannot assign NULL to struct directly. >> So, I expected them: >> 1) NULL returns if any of the elements in struct has NULL >> 2) NULL returns if all of the elements in struct have NULL >> >> By the way, my assumption was wrong in my example below. Could you let me >> know when struct is null? >> >> For example, >> create table t1(a struct<c1:int, c2:string, c3:double>); >> insert into t1 select named_struct('c1', 100, 'c2', 'test', 'c3', 1.234); >> insert into t1 select named_struct('c1', cast(null as int), 'c2', 'test', >> 'c3', 1.234); >> insert into t1 select named_struct('c1', 100, 'c2', cast(null as string), >> 'c3', 1.234); >> insert into t1 select named_struct('c1', 100, 'c2', 'test', 'c3', >> cast(null as double)); >> insert into t1 select named_struct('c1', cast(null as int), 'c2', >> cast(null as string), 'c3', cast(null as double)); >> select a is null, * from t1; >> false {"c1":100,"c2":"test","c3":1.234} >> false {"c1":null,"c2":"test","c3":1.234} >> false {"c1":100,"c2":null,"c3":1.234} >> false {"c1":100,"c2":"test","c3":null} >> false {"c1":null,"c2":null,"c3":null} >> >> Best regards, >> Jinchul >> > >
