CODEWITHSUNDEEP
google-bigquery
Deviling Master
Deviling Master

Reputation: 3113

Filtering rows from one table to another

I have two BigQuery tables:

TABLE 1
+---------+---------+---------+
|  Col1   |  Col2   |  Col3   |
+---------+---------+---------+
| Value 1 | Value 2 | Value3  |
| Value 4 | Value 5 | Value 6 |
+---------+---------+---------+

TABLE 2
+---------+---------+--------+
|  Col1   |  Col2   |  Col4  |
+---------+---------+--------+
| Value 1 | Value 2 | Value7 |
+---------+---------+--------+

I need to remove from TABLE 1 all the pairs Col1-Col2 which exists in TABLE 2

You can build test data with

WITH

TABLE1 AS (
  SELECT "Value 1" AS Col1,"Value 2" as Col2, "Value3" AS Col3
  UNION ALL
  SELECT "Value 4","Value 5", "Value 6"
),

TABLE2 AS (
  SELECT "Value 1" AS Col1,"Value 2" as Col2, "Value7" AS Col4
)

I tried this notation but it is not working

SELECT * FROM TABLE1
EXCEPT DISTINCT (
  SELECT Col1, Col2 FROM TABLE2
)

The only way I found is first filter lines and the join them together:

SELECT t1.*, t2.* EXCEPT(Col1,Col2)

FROM (
  SELECT Col1,Col2
  FROM TABLE1
  EXCEPT DISTINCT (
    SELECT Col1, Col2 FROM TABLE2
)) AS t1
LEFT JOIN TABLE1 AS t2 ON t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2

Do you know a simple way to do it using only EXCEPT function?

The output are the lines from first table filtered basing on the first 2 columns on second table

+---------+---------+---------+
|  Col1   |  Col2   |  Col3   |
+---------+---------+---------+
| Value 4 | Value 5 | Value 6 |
+---------+---------+---------+

Upvotes: 0

Views: 56

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

#standardSQL
WITH TABLE1 AS (
  SELECT "Value 1" AS Col1,"Value 2" AS Col2, "Value3" AS Col3 UNION ALL
  SELECT "Value 4","Value 5", "Value 6"
), TABLE2 AS (
  SELECT "Value 1" AS Col1,"Value 2" AS Col2, "Value7" AS Col4
)
SELECT a.*
FROM table1 a
LEFT JOIN (
  SELECT DISTINCT col1, col2
  FROM table2 
) b
USING(col1, col2)
WHERE b.col1 IS NULL   


Row Col1    Col2    Col3     
1   Value 4 Value 5 Value 6

Upvotes: 2

Related Questions