my first table looks like the following (t1)

my second table looks like the following (t2):

What I am trying to do is to find the uids (along with their car_id) from t1 that do not have a matching uid in t2. For example, the "q123" from t1 should be returned since it does not appear in t2. Any idea how this can be achieved?

Thank you!

CodePudding user response：

SELECT uid, car_id
FROM t1
WHERE uid NOT IN (SELECT uid FROM t2 )

CodePudding user response：

Using LEFT JOIN will bring back all the rows on the LEFT, regardless of a match on the right. Since you only want the non-matching, you could include only these in the WHERE clause.

SELECT t1.uid, t1.car_id, t2.category 
FROM t1 LEFT JOIN t2 on t1.uid = t2.uid 
WHERE t2.uid IS NULL

Or, you could use a NOT EXISTS

SELECT t1.uid, t1.car_id
FROM t1 
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.uid = t2.uid)

I have never used Google Bigquery, but going off the docs here, the syntax looks just like MS SQL. Also, here's the SQL Fiddle if you need it.