Hot to find records that not exists on the other table in BigQuery?

user_1158117 0
Viewed 988

I am a newbie in both SQL and BigQuery. I have a problem which to find records that do not exist on the other table.

Suppose I have table A which contains complete data and it has its own timestamp when the records were inserted, and Table B which does insert snapshot data of that day of table A.

My 1st query with "LEFT OUTER JOIN" per below:

select count(1) 
from table_a a
left outer join table_b b on (a.id =b.id)
where b.id is null

using above SQL which returned 0 results

and SQL below with "NOT EXIST":

select count(1)
from table_a a
where not exists (
        select 1
        from table_b b
        where a.id = b.id
    )

which return many records.

Now I am confused about which SQL that telling me the truth because I believe that Table B had missed one or two days of sync from Table A. How can I tell there are missing records is because I use several sample IDs on Table A that did not exist on Table B.

google-bigquery sql database
1 Answers

You can use the statement IN. This statement returns the rows from Table A, which is not found in Table B.

I will explain how it works.

Table A has all the data

enter image description here

Table B has missing rows from Table A

enter image description here

In this case, you need to know what rows are missing in table B. You will use the “in” statement. First, you will select all Rows from Table A and then you will compare the information with table B and will return the rows that are missing in table B.

You can see this example

select text
from `Table A`
where text not in (select text from `TableB`)

This was the result. The rows from table A, which are missing from table B.

enter image description here

You can see more documentation about these expression subqueries.

user_169291450
Related