how to pass null = null in the where clause

Question

I have a bigquery select statement where in some cases, the where clauses show null = null

In the case of this statement:

select * from `db.ds.table1` t1
join `db.ds.table2` t2
on t1.first_name = t2.first_name
and t1.last_name = t2.last_name

as you can see, this would appear to be looking for people, however, in some cases, I have businesses in the tables. In those cases, first_name is used, and last_name is null.

this means I have a null = null scenerio, and fails join.

How do I tell bigquery to allow null = null, without having to put coalesce statements all over the place?

Thanks

score 3 · Answer 1 · answered Nov 25 '18 at 04:09

Make the last name optional, and allow both last names being NULL as valid:

SELECT *
FROM `db.ds.table1` t1
INNER JOIN `db.ds.table2` t2
    ON t1.first_name = t2.first_name AND
       (t1.last_name = t2.last_name OR (t1.last_name IS NULL AND t2.last_name IS NULL));

We could also try to write a query using COALESCE, but it isn't really clear what value should be used in place of a NULL last name.

Mikhail Berlyant · Accepted Answer · 2018-11-25T04:45:31.067

2

#standardSQL
select * from `db.ds.table1` t1
join `db.ds.table2` t2
on t1.first_name = t2.first_name
and ifnull(t1.last_name, '') = ifnull(t2.last_name, '')

obviously ifnull is equivalent of coalesce so not sure if this is what you can accept as you mention you wanted not to use coalesce for some reason

edited Nov 25 '18 at 04:45

answered Nov 25 '18 at 04:33

Mikhail Berlyant

165,386
8
154
230

This might be OK, but in the (possibly rare) case that empty string should be recorded as either `last_name`, this query could give false results. – Tim Biegeleisen Nov 25 '18 at 13:36
1

hope OP will be able to come with the appropriate value if `''` will conflict with possible existing values for last name - if such edge cases exist – Mikhail Berlyant Nov 25 '18 at 15:18
well, I didn't want to have to put that on every field, but I guess there's no way around that. – arcee123 Nov 29 '18 at 03:12
Just to clarify - Do you mean in your real use case you have multiple such fields like last name? – Mikhail Berlyant Nov 29 '18 at 03:58

how to pass null = null in the where clause

2 Answers2