无关联同字段多表关联查询：如何规避笛卡尔积与空表无返回问题

Hey there! Let's break down how to solve this problem without hitting those unwanted Cartesian products or losing data when one table is empty.

The Core Issue #

Your three tables (Achievement, Attachment, CollegeAttended) share ProfileId and InstanceId, but there's no direct relational link between them—so joining them directly creates a cross-product (6*4=24 rows) when using inner joins, and your previous left join approach wasn't returning the 6 rows you need from Attachment. The ROW_NUMBER() method only works when Attachment has the most rows, which isn't reliable.

Feasible Solutions #

1. Keep Only Attachment's Records (Guarantee 6 Rows When Attachment Has Data)

If your goal is to return exactly one row per record in Attachment, along with any matching data from the other two tables (NULL if no match), use this approach:

First, isolate the unique ProfileId/InstanceId pairs from Attachment, then left join each table to this set of keys. This avoids cross-product because we're anchoring to Attachment's rows.

WITH AttachmentKeys AS (
    -- Get all unique key pairs from Attachment (remove DISTINCT if each pair is unique in Attachment)
    SELECT DISTINCT ProfileId, InstanceId 
    FROM Attachment
)
SELECT
    ak.ProfileId,
    ak.InstanceId,
    -- Select specific fields instead of * to avoid duplicate ProfileId/InstanceId columns
    ach.AchievementId, ach.Description, -- Example Achievement fields
    att.FileName, att.UploadDate, -- Example Attachment fields
    ca.CollegeName, ca.GraduationYear -- Example CollegeAttended fields
FROM AttachmentKeys ak
LEFT JOIN Achievement ach
    ON ak.ProfileId = ach.ProfileId 
    AND ak.InstanceId = ach.InstanceId
LEFT JOIN Attachment att
    ON ak.ProfileId = att.ProfileId 
    AND ak.InstanceId = att.InstanceId
LEFT JOIN CollegeAttended ca
    ON ak.ProfileId = ca.ProfileId 
    AND ak.InstanceId = ca.InstanceId

• Why this works: By starting with Attachment's keys, you ensure you get exactly the number of rows matching Attachment (6 in your case). Any matches from Achievement or CollegeAttended will be attached to the corresponding row; non-matches show NULL.
• If Attachment is empty: This returns 0 rows. If you need to show rows from other tables even when Attachment is empty, use the next solution.

2. Include All Key Pairs From All Tables

If you want to include every unique ProfileId/InstanceId pair that exists in any of the three tables (even if Attachment is empty), use this:

WITH AllUniqueKeys AS (
    -- Combine all unique key pairs from all three tables
    SELECT ProfileId, InstanceId FROM Achievement
    UNION
    SELECT ProfileId, InstanceId FROM Attachment
    UNION
    SELECT ProfileId, InstanceId FROM CollegeAttended
)
SELECT
    ak.ProfileId,
    ak.InstanceId,
    ach.AchievementId, ach.Description,
    att.FileName, att.UploadDate,
    ca.CollegeName, ca.GraduationYear
FROM AllUniqueKeys ak
LEFT JOIN Achievement ach
    ON ak.ProfileId = ach.ProfileId 
    AND ak.InstanceId = ach.InstanceId
LEFT JOIN Attachment att
    ON ak.ProfileId = att.ProfileId 
    AND ak.InstanceId = att.InstanceId
LEFT JOIN CollegeAttended ca
    ON ak.ProfileId = ca.ProfileId 
    AND ak.InstanceId = ca.InstanceId

• Why this works: The UNION collects all unique key pairs across all tables, so you won't miss any combinations. Each row in the result represents one unique key pair, with data from each table where available.

3. Handle Multiple Rows Per Key Pair

If any table has multiple rows for the same ProfileId/InstanceId (e.g., CollegeAttended has 2 entries for one key), use ROW_NUMBER() to pick a specific row (like the most recent one) and avoid duplicate rows in your result:

WITH CollegeAttendedRanked AS (
    SELECT *,
           -- Rank rows per key pair, ordered by a date column to get the latest entry
           ROW_NUMBER() OVER (PARTITION BY ProfileId, InstanceId ORDER BY GraduationYear DESC) AS rn
    FROM CollegeAttended
),
AttachmentKeys AS (
    SELECT DISTINCT ProfileId, InstanceId FROM Attachment
)
SELECT
    ak.ProfileId,
    ak.InstanceId,
    ach.AchievementId, ach.Description,
    att.FileName, att.UploadDate,
    ca.CollegeName, ca.GraduationYear
FROM AttachmentKeys ak
LEFT JOIN Achievement ach
    ON ak.ProfileId = ach.ProfileId 
    AND ak.InstanceId = ach.InstanceId
LEFT JOIN Attachment att
    ON ak.ProfileId = att.ProfileId 
    AND ak.InstanceId = att.InstanceId
LEFT JOIN CollegeAttendedRanked ca
    ON ak.ProfileId = ca.ProfileId 
    AND ak.InstanceId = ca.InstanceId
    AND ca.rn = 1 -- Only keep the top-ranked row per key pair

Key Takeaways #

• Always anchor your join to the set of keys you want to retain (either Attachment's keys or all keys from all tables) instead of joining the three tables directly.
• Use LEFT JOIN to preserve all rows from your anchor key set, even if there's no match in the other tables.
• Add ranking logic if you need to handle multiple rows per key pair without creating duplicates.

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