Nullpo Nullpo - 6 months ago 77
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!

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(['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    
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download