DB2中IN子句无匹配行时返回虚拟值的实现需求

Got it, let's fix this for you! The problem with your current SQL is that inner joins only return rows where there's a matching record across all joined tables. Since there's no Employee entry for SecretId '5', that ID gets filtered out entirely. To include all target IDs (even those without matches) and return your desired dummy values, we need to restructure the query to start with your list of target SecretIds, then use left joins to pull in existing data.

Step-by-Step Adjusted Query #

Here's the modified SQL that will give you exactly the result you want:

WITH TargetSecretIds AS (
    -- Create a temporary dataset with all the SecretIds you want to query
    SELECT SecretId
    FROM (VALUES ('1'), ('2'), ('3'), ('4'), ('5')) AS T(SecretId)
)
SELECT 
    -- Use COALESCE to replace NULLs with your desired values
    COALESCE(A.EmpId, T.SecretId) AS EmpId,
    COALESCE(A.EmpName, 'N/A') AS EmpName,
    COALESCE(B.Address, 'N/A') AS Address,
    COALESCE(C.OrgCode, 'N/A') AS OrgCode
FROM TargetSecretIds T
-- Left join to keep all target IDs, even if no match exists in Employee
LEFT JOIN Employee A ON T.SecretId = A.SecretId
-- Left join to Address (only pulls data if Employee exists)
LEFT JOIN Address B ON A.EmpId = B.EmpId
-- Left join to Organization (only pulls data if Employee exists)
LEFT JOIN Organization C ON A.EmpId = C.EmpId;

What This Does: #

• TargetSecretIds CTE: This creates a temporary set containing every SecretId you want to check ('1' through '5'). This ensures we don't lose any IDs from your original IN clause.
• Left Joins: Unlike inner joins, left joins preserve all rows from the left table (our TargetSecretIds set) even if there's no matching row in the right table (Employee/Address/Organization).
• COALESCE Function: This checks if the first value is NULL (meaning no match was found) and replaces it with your dummy value. For EmpId, we use the original SecretId from our target list since there's no existing EmpId for '5'.

Expected Output: #

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