如何在SQL中实现内部唯一邮箱与供应商多邮箱列的匹配查询？

Alright, let's figure out how to solve this matching problem. You've got your internal contact database with unique email addresses, and a supplier list where each entry has multiple comma-separated emails in one column—you need to link these up to send the correct contracts. Here are practical solutions based on common SQL dialects:

MySQL/MariaDB Approach #

First, let's define our table structures for context:

• internal_contacts: Stores your unique contacts with columns contact_name and unique_email (this column should have a unique constraint)
• supplier_contacts: Supplier-provided list with columns like supplier_name, contract_id, and multiple_emails (comma-separated emails, e.g., "user1@company.com, user2@company.com")

MySQL has a handy built-in function FIND_IN_SET that works perfectly for comma-separated values. We'll also clean up any spaces around commas to avoid mismatches:

SELECT
    ic.contact_name,
    sc.contract_id,
    sc.supplier_name
FROM
    internal_contacts ic
JOIN
    supplier_contacts sc 
    ON FIND_IN_SET(ic.unique_email, REPLACE(sc.multiple_emails, ' ', '')) > 0;

• REPLACE(sc.multiple_emails, ' ', '') removes any spaces between emails (since your example includes ", " instead of just ",")
• FIND_IN_SET returns the position of the internal email in the cleaned comma-separated list; if it's greater than 0, we have a match.

SQL Server Approach (2016+) #

SQL Server uses STRING_SPLIT to break comma-separated values into individual rows, which we can then join against our internal contacts:

SELECT
    ic.contact_name,
    sc.contract_id,
    sc.supplier_name
FROM
    internal_contacts ic
JOIN
    supplier_contacts sc
CROSS APPLY
    STRING_SPLIT(REPLACE(sc.multiple_emails, ' ', ''), ',') AS split_emails
WHERE
    ic.unique_email = split_emails.value;

• CROSS APPLY acts like a join that runs the STRING_SPLIT function for every row in supplier_contacts, creating a row for each email in the list.
• We filter to only rows where the internal unique email matches one of the split supplier emails.

PostgreSQL Approach #

PostgreSQL offers two solid options here: using unnest with string_to_array, or using array containment:

Option 1: Split and Join #

SELECT
    ic.contact_name,
    sc.contract_id,
    sc.supplier_name
FROM
    internal_contacts ic
JOIN
    supplier_contacts sc
JOIN
    unnest(string_to_array(REPLACE(sc.multiple_emails, ' ', ''), ',')) AS split_emails(email)
ON
    ic.unique_email = split_emails.email;

• string_to_array converts the cleaned comma-separated string into an array of emails
• unnest turns that array into individual rows, which we join against our internal contacts.

Option 2: Array Containment Check #

SELECT
    ic.contact_name,
    sc.contract_id,
    sc.supplier_name
FROM
    internal_contacts ic
JOIN
    supplier_contacts sc
ON
    string_to_array(REPLACE(sc.multiple_emails, ' ', ''), ',') @> ARRAY[ic.unique_email];

• The @> operator checks if the supplier's email array contains the internal unique email—this is a concise way to write the match without splitting into rows.

Key Notes to Avoid Headaches #

• Clean Your Data: Always remove extra spaces (like we did with REPLACE) and consider normalizing case (e.g., LOWER(ic.unique_email) = LOWER(split_emails.value)) because email addresses are case-insensitive.
• Performance: If your supplier list is large, queries that split comma-separated values can be slow. For long-term maintainability, consider restructuring the supplier data into a separate supplier_emails table (one row per email, linked to the supplier/contract) instead of storing multiple emails in a single column.
• Duplicate Matches: If an internal email appears in multiple supplier rows, your results will include all those matches. Add DISTINCT or adjust your WHERE clause if you only need one match per contact.

内容的提问来源于stack exchange，提问作者MattC