SQL Question
Left join with string function?
I need to left join two tables:
- One table has emails
- The other table, is a domain blacklist.
I did something like this:
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))
But throws me an error:
"LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join."
Someone know how can I solve this?
Thanks!
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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(['[email protected]','[email protected]','[email protected]','[email protected]']) 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
[email protected] valid
[email protected] valid
[email protected] invalid
[email protected] valid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download