三表混合查询求助：ID存在性不确定，Join/Union均丢失数据

Hey there! Let's work through this problem together—sounds like you're dealing with the classic "missing data when joining tables with optional IDs" headache. Here are a few targeted approaches to try that should preserve all your records:

1. Start with a Full Set of All IDs, Then Left Join Each Table #

Instead of trying to join tables directly (which can drop records when IDs don't match), first collect every unique ID across all three tables, then left join each table to this master list. This guarantees you won't lose any ID that exists in just one or two tables:

-- First, get every unique ID from all three tables
WITH all_unique_ids AS (
    SELECT id FROM table1
    UNION
    SELECT id FROM table2
    UNION
    SELECT id FROM table3
)
-- Now left join each table to this ID set
SELECT
    ai.id,
    t1.column1,
    t2.column2,
    t3.column3
FROM all_unique_ids ai
LEFT JOIN table1 t1 ON ai.id = t1.id
LEFT JOIN table2 t2 ON ai.id = t2.id
LEFT JOIN table3 t3 ON ai.id = t3.id;

The UNION here automatically removes duplicate IDs, so you'll only have one entry per unique ID in the CTE.

2. Use UNION ALL + Aggregation to Combine Records #

If joins feel too restrictive, you can "unpivot" each table into a single combined dataset, then aggregate to roll up records by ID. This works great when you need to pull in all fields even if an ID only exists in one table:

SELECT
    COALESCE(t1.id, t2.id, t3.id) AS unified_id,
    MAX(t1.column1) AS table1_column,
    MAX(t2.column2) AS table2_column,
    MAX(t3.column3) AS table3_column
FROM (
    -- Include all records from each table, with NULLs for missing columns
    SELECT id, column1, NULL AS column2, NULL AS column3 FROM table1
    UNION ALL
    SELECT id, NULL AS column1, column2, NULL AS column3 FROM table2
    UNION ALL
    SELECT id, NULL AS column1, NULL AS column2, column3 FROM table3
) AS combined_records
GROUP BY COALESCE(t1.id, t2.id, t3.id);

Using UNION ALL instead of UNION ensures you don't accidentally drop duplicate records from the same table (if that's something you need to keep). The MAX() function picks up the non-NULL value for each ID across the tables.

3. Try Full Outer Joins (With Coalesce for ID Matching) #

If you prefer sticking with joins, full outer joins will preserve records from all tables, even when there's no matching ID. You'll need to use COALESCE to handle cases where IDs are missing in one table:

SELECT
    COALESCE(t1.id, t2.id, t3.id) AS unified_id,
    t1.column1,
    t2.column2,
    t3.column3
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.id = t2.id
FULL OUTER JOIN table3 t3 ON COALESCE(t1.id, t2.id) = t3.id;

This way, even if an ID only exists in table3, it will still show up in the results.

A quick note: If you were using SELECT DISTINCT before, double-check if it's actually necessary—these approaches should naturally handle duplicates without needing it, unless you have duplicate rows within a single table (in which case you might want to deduplicate that table first with a subquery).

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