CODEWITHSUNDEEP
sqlgoogle-bigquery
Norah Jones
Norah Jones

Reputation: 467

BigQuery SQL exclude rows where certain values appear in different columns

I have a table:

|-------------|------------------|-------------|------------------|
|      id     |     value_1      |  value_2    |     value_3      |
|-------------|------------------|-------------|------------------| 
|       1     |         10       |      11     |         111      |
|-------------|------------------|-------------|------------------|
|       2     |         20       |      22     |         222      |
|-------------|------------------|-------------|------------------|
|       3     |         30       |      33     |         333      |
|-------------|------------------|-------------|------------------|
|       4     |         40       |      44     |         444      |
|-------------|------------------|-------------|------------------|
|       5     |         50       |      55     |         555      |
|-------------|------------------|-------------|------------------|
|       6     |         60       |      66     |         666      |
|-------------|------------------|-------------|------------------|
|       7     |         60       |     null    |        null      |
|-------------|------------------|-------------|------------------|

I would like to exclude rows which contain

I've tried code like this:

select *
from (
    select *
    from (
        select *
        from `schema.table`
        where value_3 not in ('444', '555'))
    where value_2 not in ('33', '44'))
where value_1 not in ('10', '20'))

and also liked this:

select *
from `schema.tabele`
where id not in (
  select distinct id
  from `schema.tabele`
  where id not in (
    select distinct id
    from `schema.tabele`
    where id not in (
      select distinct id
      from `schema.tabele`
      where value_3 not in ('444', '555')))
  and value_2 not in ('33', '44'))
and value_1 not in ('10', '20'))

I am not getting the good results.. Could you explain why?

Upvotes: 0

Views: 2094

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This seems overly complicated. How about using and?

select *
from `schema.table`
where value_3 not in ('444', '555') and
      value_2 not in ('33', '44') and
      value_1 not in ('10', '20')

EDIT:

If you need to handle NULL values, then you need to be explicit:

select *
from `schema.table`
where (value_3 not in ('444', '555') or value_3 is null) and
      (value_2 not in ('33', '44') or value_2 is null) and
      (value_1 not in ('10', '20') or value_1 is null)

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

below is for BigQuery Standard SQL

#standardSQL
select *
from `project.dataset.table`
where not ( 
  ifnull(value_3, 'x') in ('444', '555')
  or ifnull(value_2, 'x') in ('33', '44')
  or ifnull(value_1, 'x') in ('10', '20')
  )

Upvotes: 1

Related Questions