CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Moose
Moose

Reputation: 83

BigQuery Difference Between 2 Tables

I'm trying to find and return rows that DON'T match, while searching between 2 tables

Both tables have the same 3 columns. Both tables have the exact same amount of IDs. The tables are "a" and "b"

Cols; (Id, country, passfail)

I'm trying to return all the rows where passfail does not match between the tables (where id's match)

I've tried to run 2 queries but cant seem to print the resulting rows.

SELECT a.*
FROM a
    LEFT JOIN b ON (a.passfail = b.passfail)
WHERE a.passfail IS NULL

SELECT a.* 
FROM a 
AS a JOIN b 
AS b 
ON a.passfail = b.passfail 
WHERE a.passfail != b.passfail

Neither of those queries seems to work for me.

Any help or recommendations are appreciated!

Upvotes: 0

Views: 3079

Answers (1)

savac
savac

Reputation: 301

You must join on the id field. Here is an example:

WITH a as (SELECT * FROM UNNEST(ARRAY<STRUCT<id INT64, country STRING, passfail BOOL>>[(1, 'uk', true), (3, 'fr', false)])),
     b as (SELECT * FROM UNNEST(ARRAY<STRUCT<id INT64, country STRING, passfail BOOL>>[(1, 'uk', true), (3, 'fr', true)]))

SELECT
  a.*
FROM
  a
LEFT JOIN
  b
ON
  (a.id = b.id)
WHERE
  a.passfail != b.passfail

Upvotes: 2

Related Questions