My query has a NOT IN, and suddenly stopped showing results
I have a query that runs every day, and suddenly it stopped outputting results. It doesn't output an error, just no results.
Note that this query includes a NOT IN operator.
google-bigquery
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
add a comment |
I have a query that runs every day, and suddenly it stopped outputting results. It doesn't output an error, just no results.
Note that this query includes a NOT IN operator.
google-bigquery
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
add a comment |
I have a query that runs every day, and suddenly it stopped outputting results. It doesn't output an error, just no results.
Note that this query includes a NOT IN operator.
google-bigquery
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
I have a query that runs every day, and suddenly it stopped outputting results. It doesn't output an error, just no results.
Note that this query includes a NOT IN operator.
google-bigquery
google-bigquery
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
asked Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
22.4k252120
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This is a known problem of NOT IN behavior, check out the MySQL version of it:
- MySQL "Not IN" query suddenly stopped returning results
With BigQuery:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3
But then if you have a null in data2:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results
Instead you could do a LEFT JOIN, or a NOT EXISTS:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
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%2f53308182%2fmy-query-has-a-not-in-and-suddenly-stopped-showing-results%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is a known problem of NOT IN behavior, check out the MySQL version of it:
- MySQL "Not IN" query suddenly stopped returning results
With BigQuery:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3
But then if you have a null in data2:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results
Instead you could do a LEFT JOIN, or a NOT EXISTS:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
add a comment |
This is a known problem of NOT IN behavior, check out the MySQL version of it:
- MySQL "Not IN" query suddenly stopped returning results
With BigQuery:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3
But then if you have a null in data2:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results
Instead you could do a LEFT JOIN, or a NOT EXISTS:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
add a comment |
This is a known problem of NOT IN behavior, check out the MySQL version of it:
- MySQL "Not IN" query suddenly stopped returning results
With BigQuery:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3
But then if you have a null in data2:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results
Instead you could do a LEFT JOIN, or a NOT EXISTS:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
This is a known problem of NOT IN behavior, check out the MySQL version of it:
- MySQL "Not IN" query suddenly stopped returning results
With BigQuery:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3
But then if you have a null in data2:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results
Instead you could do a LEFT JOIN, or a NOT EXISTS:
WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
answered Nov 14 '18 at 20:22
Felipe HoffaFelipe Hoffa
22.4k252120
22.4k252120
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%2f53308182%2fmy-query-has-a-not-in-and-suddenly-stopped-showing-results%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
