Oracle SQL实现table1与table2联合，补全缺失编码并过滤指定值

Let's break this down properly—first, let's recap the scenario with your sample data to make sure we're on the same page:

Sample Table Data #

table1

column1 column2 column3
000001  ABC     COD1
000001  ABC     COD2
000002  BCD     COD3
000003  EDF     COD1
000003  EDF     COD3
000004  FGH     COD1
000004  FGH     COD2
000004  FGH     COD3
000004  FGH     COD4

table2 (filtered to only keep COD1-COD4, since we exclude COD5-COD7)

column3
COD1
COD2
COD3
COD4

Your Exact Requirements #

• For every distinct (column1, column2) pair from table1, generate a row for each of the 4 target codes (COD1-COD4)
• If the code exists for that pair in table1, keep the actual column1/column2 values
• If the code is missing for the pair, fill column1/column2 with null
• No duplicate valid records (so we don't repeat existing (col1,col2,col3) entries), but null rows can repeat per pair

Working Oracle SQL Solution #

Here's the query that will produce exactly the output you're looking for:

SELECT
    CASE WHEN match.col1 IS NOT NULL THEN pairs.col1 ELSE NULL END AS column1,
    CASE WHEN match.col1 IS NOT NULL THEN pairs.col2 ELSE NULL END AS column2,
    codes.col3
FROM (
    -- Get all unique (column1, column2) groups from table1
    SELECT DISTINCT column1, column2
    FROM table1
) pairs
-- Cross join to create every possible combination of group + target code
CROSS JOIN (
    SELECT column3
    FROM table2
    WHERE column3 IN ('COD1', 'COD2', 'COD3', 'COD4')
) codes
-- Left join back to table1 to check if the (group + code) combination exists
LEFT JOIN table1 match
    ON pairs.column1 = match.column1
    AND pairs.column2 = match.column2
    AND codes.column3 = match.column3
-- Order to match your expected output structure
ORDER BY pairs.column1 NULLS LAST, pairs.column2 NULLS LAST, codes.col3;

How This Works #

1. Step 1: Get Unique Groups: The subquery pairs pulls all distinct (column1, column2) pairs from table1—this gives us each "group" we need to generate code rows for.
2. Step 2: Generate All Combinations: Cross joining pairs with the filtered codes subquery creates every possible (col1, col2, col3) combination for our target codes. This ensures we don't miss any rows we need.
3. Step 3: Validate Existence: The left join to table1 (aliased as match) checks if that specific combination actually exists in the original data.
4. Step 4: Fill Nulls: The CASE statements show the original col1/col2 values only if a match exists; otherwise, they output null.
5. Step 5: Order Correctly: The ORDER BY clause ensures the output is grouped by each (col1, col2) pair and ordered by code, matching the structure you provided.

Why Your Earlier Attempts Might Have Failed #

Using UNION/MINUS or basic right joins can struggle here because they don't first generate all possible combinations of groups and codes. Without that cross join step, you end up either missing null rows or creating duplicates. This approach fixes that by explicitly building out every required row first, then filling in the valid data where it exists.

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