使用WHERE IN子句后SQL JOIN语句遇问题，附重复联系人查询SQL

Hey there! Let's tackle that JOIN problem you're facing with your duplicate contact query. Your original WHERE IN approach works, but when switching to JOINs, it's easy to run into issues like duplicate rows or unexpected results—let's break this down and fix it.

First, Why Your Original Query Works #

Your existing WHERE IN logic correctly isolates contacts with duplicate emails or phones by:

1. Finding emails that appear more than once (and aren't empty)
2. Finding phones that appear more than once (and aren't empty)
3. Returning any contact that matches either list

The problem with translating this directly to JOINs is that combining two JOINs (one for emails, one for phones) can create cartesian products (duplicate rows) if a contact has both a duplicate email and phone. Let's fix that.

Approach 1: Use UNION to Combine Separate JOINs #

This is the cleanest and most efficient method, as it avoids duplicate rows entirely by merging two distinct result sets:

SELECT t1.contactid, t1.firstname, t1.email, t1.phone
FROM vtiger_contactscf t1
JOIN (
    -- Get all duplicate non-empty emails
    SELECT email 
    FROM vtiger_contactscf 
    WHERE CHAR_LENGTH(email) > 0 
    GROUP BY email 
    HAVING COUNT(*) > 1
) dup_emails ON t1.email = dup_emails.email

UNION -- Automatically removes duplicate rows

SELECT t1.contactid, t1.firstname, t1.email, t1.phone
FROM vtiger_contactscf t1
JOIN (
    -- Get all duplicate non-empty phones
    SELECT phone 
    FROM vtiger_contactscf 
    WHERE CHAR_LENGTH(phone) > 0 
    GROUP BY phone 
    HAVING COUNT(*) > 1
) dup_phones ON t1.phone = dup_phones.phone;

Why this works:

• The first part pulls contacts with duplicate emails, the second pulls those with duplicate phones.
• UNION merges the two lists and removes any duplicates (e.g., a contact with both a duplicate email and phone will only appear once).
• If you want to keep duplicate rows (though you probably don't here), use UNION ALL instead.

Approach 2: LEFT JOINs with DISTINCT #

If you prefer a single JOIN-based query, you can use LEFT JOINs combined with DISTINCT to eliminate duplicate rows:

SELECT DISTINCT t1.contactid, t1.firstname, t1.email, t1.phone
FROM vtiger_contactscf t1
LEFT JOIN (
    SELECT email 
    FROM vtiger_contactscf 
    WHERE CHAR_LENGTH(email) > 0 
    GROUP BY email 
    HAVING COUNT(*) > 1
) dup_emails ON t1.email = dup_emails.email
LEFT JOIN (
    SELECT phone 
    FROM vtiger_contactscf 
    WHERE CHAR_LENGTH(phone) > 0 
    GROUP BY phone 
    HAVING COUNT(*) > 1
) dup_phones ON t1.phone = dup_phones.phone
-- Filter to only contacts that have a duplicate email OR phone
WHERE dup_emails.email IS NOT NULL OR dup_phones.phone IS NOT NULL;

Why this works:

• LEFT JOIN ensures we keep all contacts, but the WHERE clause filters to only those with a match in either duplicate list.
• DISTINCT removes duplicate rows that would otherwise appear if a contact matches both the email and phone duplicate lists.

Common Pitfalls to Avoid #

• Cartesian Products: If you use INNER JOIN for both duplicate lists without UNION or DISTINCT, you'll get multiple rows for contacts that match both criteria (one for each email duplicate + one for each phone duplicate).
• Case Sensitivity: If your database is case-sensitive, emails like John@Example.com and john@example.com won't be seen as duplicates. Fix this by using LOWER():

  ON LOWER(t1.email) = LOWER(dup_emails.email)
  

• Empty Values: Your original query already filters out empty emails/phones with CHAR_LENGTH(email) > 0—keep this to avoid matching empty strings which might appear multiple times.

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