CODEWITHSUNDEEP
sqlgoogle-bigquery
Amar
Amar

Reputation: 175

I want to get records from BigQuery table which has atleast one of the two needed value

My BigQuery Schema has suppose company_name | email | email_2 | phone | mobile |and there are about 50K records into the table. I want to fetch records that at least have mention of a phone number and email. Needs rows with eg. email phone email mobile
email_2 phone email_2 mobile email email_2 phone email email_2 phone mobile

What's the efficient code for this, as i am new to both SQL and BigQuery?

FROM Property_Dataset.pmDATA 
WHERE 
(email IS NOT NULL AND phone IS NOT NULL) OR
(email IS NOT NULL AND mobile IS NOT NULL) OR
(email_2 IS NOT NULL AND phone IS NOT NULL) OR
(email_2 IS NOT NULL AND mobile IS NOT NULL);

Upvotes: 0

Views: 128

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

I think below is most compact and scalable (in terms of number of involved columns) - for BigQuery Standard SQL 

#standardSQL
SELECT *
FROM `project.Property_Dataset.pmDATA`
WHERE NOT COALESCE(email, email_2) IS NULL
AND NOT COALESCE(phone, mobile) IS NULL

Upvotes: 2

Eray Balkanli
Eray Balkanli

Reputation: 7960

I think you can use a where clause like below:

select *
from Property_Dataset.pmDATA 
where (email is not null or email_2 is not null) AND (phone is not null or mobile is not null)

Upvotes: 2

Related Questions