Hive where int/string ＜＞ int/int/string ＜＞ string cast类型转化/where自动过滤掉NULL，保留‘‘

Dear 丶 2021-09-23 05:00 323阅读 0赞

### 1 int/string <> int，会把string cast成 int ,不能转化为int的字符串都会cast成NULL，<>int也会把NULL值的记录过滤掉，''不是NULL故''保留在结果中  ###

select cast('' as int) --res7，结果是NULL
    select cast(' ' as int) --res8，结果是NULL 
    select cast(null as int) -- 结果是NULL 
    select cast('1' as int)  --结果是1          
    select cast('' as string) --结果是''
    
    
    select *
    from (
    select 1 as c1
    
    union all
    select 2 as c1
    
    union all
    select '34' as c1
    
    union all
    select '3ab4' as c1
    
    union all
    select 'ab' as c1
    
    union all
    select '' as c1
    
    union all
    select ' ' as c1
    
    union all
    select null as c1
    ) t1  where c1 <> 1 ; --res15，2行记录分别是2，34，不能转化为int的字符串都会cast成NULL，<>1会把NULL过滤掉

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70][]

select *
    from (
    select '1' as c1
    
    union all
    select '2' as c1
    
    union all
    select '' as c1
    
    union all
    select ' ' as c1
    
    union all
    select null as c1
    
    ) t1  where c1 <> 1 ;  --res10,1行记录是2

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1][]

select *
    from (
    select 1 as c1
    
    union all
    select 2 as c1
    
    union all
    select '' as c1
    
    union all
    select ' ' as c1
    
    union all
    select null as c1
    ) t1  where c1 <> 1 ; --res5,1行记录是2

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 2][] ###

## ##

### 2 int/string <> string，会把int cast成string，<>string也会把NULL值的记录过滤掉，''不是NULL故''保留在结果中   ###

select *
    from (
    select 1 as c1
    
    union all
    select 2 as c1
    
    union all
    select '34' as c1
    
    union all
    select '3ab4' as c1
    
    union all
    select 'ab' as c1
    
    union all
    select '' as c1
    
    union all
    select ' ' as c1
    
    union all
    select null as c1
    ) t1  where c1 <> '1'  -- res16,结果共6行，分别是2，34，3ab4,ab,'',' ',NULL值被过滤掉

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 3][] ###

select *
    from (
    select 1 as c1
    union all
    select 2 as c1
    
    union all
    select '' as c1
    
    union all
    select ' ' as c1
    
    union all
    select null as c1
    ) t1  where c1 <> '1' ;    --res9,3行记录,分别是2，'',' '

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 4][]

以上Hive2.0.0

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70]: /images/20210923/842684a68d1d4513bf507781e80883ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1]: /images/20210923/7dd317335d604960b1aa8c3c25cfab7d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 2]: /images/20210923/7cae49df8b50423e9b9e91f2a50bb56b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 3]: /images/20210923/d474e2290952430893ace3fb7f46e803.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 4]: /images/20210923/d5a6bf572f8b485fafe9b3e446566a5e.png