Exclude Common Email Providers — Pattern Matching in SQL

The problem

Brightlane's outreach manager is excluding customers who use major free email providers from a B2B campaign list.

Write a query to return the ID, name, and email of every customer whose email address does not contain gmail, yahoo, or hotmail.

Assumptions:

The customers table has one row per customer with an id, a name, and an email.
A qualifying customer has an email that does not contain any of the substrings gmail, yahoo, or hotmail anywhere in the string.

Output:

One row per qualifying customer, with columns id, name, and email.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name,
  email
FROM
  customers
WHERE
  email !~ 'gmail|yahoo|hotmail'

The shape

!~ is the negated POSIX regex operator, and the pattern gmail|yahoo|hotmail is three substrings joined by alternation. Together the predicate means "the email does not contain any of these three substrings." Negating a single regex that lists every excluded domain is shorter than negating three separate LIKE predicates joined by AND.

Clause by clause

SELECT id, name, email returns the three columns the outreach list needs. Showing the email lets the reader confirm each row is a B2B-style address.
FROM customers reads the customer table.
WHERE email !~ 'gmail|yahoo|hotmail' keeps rows whose email does not match the pattern. The | separates three alternatives; the row passes only when none of the three substrings appear anywhere in the email. !~ is the case-sensitive negation, which is fine here because the domain strings in the data are all lowercase.

Why this and not `AND` of three `NOT LIKE` predicates

email NOT LIKE '%gmail%' AND email NOT LIKE '%yahoo%' AND email NOT LIKE '%hotmail%' returns the same rows. The regex form expresses the same exclusion as one predicate with three alternatives, and adding a fourth excluded domain costs three more characters instead of a fourth full clause. Either spelling is correct. The regex stays one line as the list grows.

The trap

NULL behavior. If a customer row had an email of NULL, the regex would return NULL and WHERE would treat that as not-true, silently dropping the row. The reference data has no NULL emails so the issue does not surface here, but on any real customer table the predicate email !~ 'pattern' OR email IS NULL would be needed to keep rows that have no recorded email at all.

You practiced !~ 'a|b|c' — case-sensitive negated regex with alternation, the inverse of a multi-substring match expressed in one pattern.

Return the ID, name, and email of every customer whose email address does not contain `gmail`, `yahoo`, or `hotmail`

The shape

Clause by clause

Why this and not `AND` of three `NOT LIKE` predicates

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not AND of three NOT LIKE predicates

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `AND` of three `NOT LIKE` predicates