E9nE5Lr5

1CREATE DATABASE IF NOT EXISTS test;
2USE test;
3DROP TABLE IF EXISTS student_info;
4CREATE TABLE IF NOT EXISTS student_info(
5id string COMMENT 'student id',
6name string COMMENT 'student name'
7)
8PARTITIONED BY (l_date string)
9ROW FORMAT DELIMITED
10FIELDS TERMINATED BY 't';
11ALTER TABLE test.student_info SET SERDEPROPERTIES('serialization.null.format' = '');
12
13DROP TABLE IF EXISTS student_score;
14CREATE TABLE IF NOT EXISTS student_score(
15id string COMMENT 'student id',
16class string COMMENT 'class',
17score int COMMENT 'class score'
18)
19PARTITIONED BY (l_date string)
20ROW FORMAT DELIMITED
21FIELDS TERMINATED BY 't';
22ALTER TABLE test.student_score SET SERDEPROPERTIES('serialization.null.format' = '');
23	
241   jobs
252   cook
263   gates
274   musk
28	
291   math    98
302   math    96
313   math    94
32	
33select * from test.student_info a
34left join test.student_score b
35on a.id=b.id
36where (b.id='' or b.id is null)
37and a.id='4';
38	
39select * from test.student_info a
40left join test.student_score b
41on a.id=b.id
42where (b.id='' or b.id is null)
43and trim(a.id)='4';
44	
45a.id    a.name  b.id    b.class b.score
464   musk    NULL    NULL    NULL