CODEWITHSUNDEEP
sqlgoogle-bigquery
asmrt
asmrt

Reputation: 11

BigQuery/I can't correctly result using "in" clauses

I want to extract perfectly match data, not partical match data.
But, I can't extract them, if I execute sql code of the below:

I estimate this sql code extract no data , but this extract all rows of data.

【SQL code】

WITH a AS(
    SELECT
        001 AS id_a,
        112345678901234567 AS x
    UNION ALL
    SELECT
        002,
        112345678901233567
    UNION ALL
    SELECT
        003,
        112345678901232568
),
comp_a AS(
    SELECT
        *
    FROM
        a
    WHERE
        x IN(112345678901234000, 112345678901233000, 112345678901232000)
),
comp_b AS(
    SELECT
        004 AS id_b
    UNION ALL
    SELECT
        005
)
SELECT
    id_a,
    id_b
FROM
    comp_a
    LEFT OUTER JOIN
        comp_b
    ON  (
            comp_a.id_a = comp_b.id_b
        )
WHERE
    comp_b.id_b IS NULL
;

I think "in" clauses are used for perfectly match. But, perhaps, I think this sql code isn't executed "in" clauses , but it is executed "like" clauses.

I will be glad you answer solution of my question.

■Further note:
　・I deleted cashe of browser and Bigquery. But I couldn't solve it.
　・This sql code is sample code , because I can't expose real sql code.
・I can recreate this problem in One enviroment of BigQuery,
but I can't recreate in Other enviroment of BigQuery.
This Problem may be not problem of sql code , but problem of enviroment
or setting.

Upvotes: 0

Views: 73

Answers (1)

asmrt
asmrt

Reputation: 11

Thank you for answering my question.

I solved my question. The cause of my problem is not BigQuery , but it is the format of Excel.

Detail: I tried to check data using Excel , Because the results are a lot of data. Sad to say , Because of the format of Excel is numeric type , a part of number data are rounded. So I misunderstood the correct result to the wrong result.

Sorry about my misunderstanding.

Upvotes: 1

Related Questions