my first table looks like the following (t1)
| uid | car_id |
|---|---|
| s123 | 1234 |
| p908 | 2345 |
| q123 | 567 |
my second table looks like the following (t2):
| uid | category |
|---|---|
| s123 | honda |
| p908 | mercedes |
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!
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.