Remove specific rows with condition in two columns BQ

Question

I have a table with 700 rows looking like this:

Among all 700 rows, I want to remove 8 rows (highlighted). I've tried everything I could but nothing seems to work.

For example I tried:

SELECT *
FROM xx.yy.zz
WHERE column1 NOT IN (
      SELECT column1 
      FROM xx.yy.zz
      WHERE column1 IN ('2 - K','3 - K','19-1 - K','33 - K') AND column2 = 2020)
AND column1 NOT IN (
      SELECT column1 
      FROM xx.yy.zz
      WHERE column1 IN ('304 - K','303 - K','2 - K','3 - K') AND column2 = 2021)

But it seems it takes out more rows then it should. What am I doing wrong?

user_1144035 · Answer

You can just use boolean expressions:

select t.*
from xyz
where not ( ( column_2 = 2020 and column_1 in ('2 - K','3 - K','19-1 - K','33 - K')
            ) or
            ( column_2 = 2020 and column_1 in ('304 - K','303 - K','2 - K','3 - K')
            )
          )

No subquery is needed.

user_15959787 · Answer

Or you can try below:

SELECT *
FROM xx.yy.zz
WHERE column1||column2 NOT IN  
('2 - K2020','3 - K2020','19-1 - K2020','33 - K2020','304 - K2021','303 - K2021','2 - K2021','3 - K2021')

user_5221944 · Answer

I noticed, you have duplicate entries in your example - like 9 - K | 2019
If in your use case it is ok to remove such duplicate values - below is an option for you to consider

with exclude_list as (
select * from unnest([    
    ('2 - K', 2020),
    ('3 - K', 2020),
    ('19-1 - K', 2020),
    ('33 - K', 2020),
    ('304 - K', 2021),
    ('303 - K', 2021),
    ('2 - K', 2021),
    ('3 - K', 2021)
  ])
)
select * from `xx.yy.zz`
except distinct select * from exclude_list

if applied to sample data in your question - output is

Note: here you are getting back 10 rows (not 11) because one duplicate row was eliminated

If you need to preserve dups - below option should be considered

with exclude_list as (
select * from unnest([    
    ('2 - K', 2020),
    ('3 - K', 2020),
    ('19-1 - K', 2020),
    ('33 - K', 2020),
    ('304 - K', 2021),
    ('303 - K', 2021),
    ('2 - K', 2021),
    ('3 - K', 2021)
  ])
)
select * from `xx.yy.zz`
where not (column1, column2) in 
(select as struct * from exclude_list)

in this case - output will have 11 rows as in below

Remove specific rows with condition in two columns BQ

3 Answers