Left outer join in BigQuery on multiple keys doesn't if one of them is null -

i ran odd left outer joins on multiple keys in bigquery. if 1 of keys null on right table doesn't match , therefore puts null in values right table.

if i've got 2 tables

   table1              table2 ------------      ----------------  k1 |k2  |v3      k1 |k2  | v4 ------------      ----------------  foo|boo |hey     foo|null|  bar|test|yo      bar|test| hi  foo|null|hey 

and following join

select t1.k1, t1.k2, t1.v3, t2.v4 [table1] t1 left outer join each [table2] t2  on t1.k1=t2.k1 , t1.k2=t2.k2 

i result this

t1_k1|t1_k2|t1_v3|t2_v4 ----------------------- foo  |boo  |hey  |null  --no match here null in t2_v4 it's ok bar  |test |hey  |hi    --it matches here on bar , test foo  |null |hey  |null  --it doesn't match on foo null. 

i expect last row was

foo  |null |hey  |you 

is expected behaviour? mean no matches on null.

is there other way of producing result want?

in sql, null not equal anything--even itself.

gordon's answer has reasonable suggestions, note bigquery supports join conditions conjunctions of equalities, rules out use of or or is null.

can use sentinel value other null? example, if replace null empty string (or string "null", or else doesn't occur elsewhere in data), join work expect. can on fly using subquery, @ minimal performance cost.

select t1.k1, t1.k2, t1.v3, t2.v4   (select ifnull(k1, "null") k1, ifnull(k2, "null") k2 [table1]) t1 left outer join each   (select ifnull(v3, "null") v3, ifnull(v4, "null") v4 [table2]) t2 on t1.k1 = t2.k1 , t1.k2 = t2.k2 

you can add outer select turn string "null" real null.

obviously works if string "null" doesn't occur elsewhere in data.