how to pass null = null in the where clause
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
sql google-bigquery
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
add a comment |
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
sql google-bigquery
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
add a comment |
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
sql google-bigquery
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
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
sql google-bigquery
sql google-bigquery
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
asked Nov 25 '18 at 4:04
arcee123arcee123
75611536
75611536
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
#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
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
This might be OK, but in the (possibly rare) case that empty string should be recorded as eitherlast_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 3: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 3:58
add a comment |
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.
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53464556%2fhow-to-pass-null-null-in-the-where-clause%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
#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
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
This might be OK, but in the (possibly rare) case that empty string should be recorded as eitherlast_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 3: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 3:58
add a comment |
#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
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
This might be OK, but in the (possibly rare) case that empty string should be recorded as eitherlast_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 3: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 3:58
add a comment |
#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
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
#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
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
edited Nov 25 '18 at 4:45
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
answered Nov 25 '18 at 4:33
Mikhail BerlyantMikhail Berlyant
57.4k43571
57.4k43571
This might be OK, but in the (possibly rare) case that empty string should be recorded as eitherlast_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 3: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 3:58
add a comment |
This might be OK, but in the (possibly rare) case that empty string should be recorded as eitherlast_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 3: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 3:58
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
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
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
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 3:12
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 3: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 3:58
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 3:58
add a comment |
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.
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
add a comment |
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.
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
add a comment |
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.
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
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.
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
answered Nov 25 '18 at 4:09
Tim BiegeleisenTim Biegeleisen
222k1389142
222k1389142
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53464556%2fhow-to-pass-null-null-in-the-where-clause%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
