I need to left join two tables:
SELECT
CASE
WHEN b.domain IS NULL then "Invalid"
ELSE "Valid"
END as Validated
FROM Emails e
LEFT JOIN DomainBlacklist b
ON ENDS_WITH(LOWER(e.email), LOWER(b.domain))
Below is for BigQuery Standard SQL
#standardSQL
SELECT email,
IF(MAX(ENDS_WITH(LOWER(email), LOWER(domain))), 'invalid', 'valid') AS Validated
FROM `project.dataset.Emails`
CROSS JOIN `project.dataset.DomainBlacklist`
GROUP BY email
You can test / play with above query using dummy data as below
#standardSQL
WITH `project.dataset.Emails` AS (
SELECT email
FROM UNNEST(['user1@abc.com','user2@abc.com','user3@uvw.com','user4@xyz.com']) AS email
), `project.dataset.DomainBlacklist` AS (
SELECT domain
FROM UNNEST(['uvw.com','qwe.net']) AS domain
)
SELECT email,
IF(MAX(ENDS_WITH(LOWER(email), LOWER(domain))), 'invalid', 'valid') AS Validated
FROM `project.dataset.Emails`
CROSS JOIN `project.dataset.DomainBlacklist`
GROUP BY email
result is
email Validated
user1@abc.com valid
user2@abc.com valid
user3@uvw.com invalid
user4@xyz.com valid