两张同主键表的差异数据查询：现有T-SQL实现优化咨询

Hey there! Your existing query gets the job done, but we can streamline it for better performance and readability. Here's a more optimized approach that combines both your target cases into a single, efficient query:

Optimized Query #

SELECT 
    B.[ID],
    B.[Name],
    CASE
        WHEN A.[ID] IS NULL THEN 'Data did not exist before'
        WHEN A.[Name] <> B.[Name] THEN 'Data has changed'
    END AS [Comment]
FROM TABLENAMEB B
LEFT JOIN #TABLENAMEA A ON B.[ID] = A.[ID]
WHERE 
    A.[ID] IS NULL -- Rows new to Table B (no matching ID in Table A)
    OR A.[Name] <> B.[Name] -- Rows where Name value changed from Table A to B

Why This Is Better #

• Single Pass Efficiency: Instead of running two separate queries and merging results with UNION, this approach joins the tables once and filters in a single scan. This cuts down on I/O and execution time, especially with large datasets.
• Cleaner Maintainability: All logic (detecting new/changed rows + labeling comments) lives in one query, making it easier to understand and modify later.
• No Unnecessary Overhead: UNION automatically deduplicates rows (even though your primary key ID should prevent duplicates here), adding extra processing. This query skips that step entirely.

Handling NULL Values in Name #

If the Name field can contain NULL values, note that NULL <> any_value returns UNKNOWN in SQL—so those cases won't be captured by the basic condition. To treat "one side NULL, the other not" as a change, use this adjusted version:

SELECT 
    B.[ID],
    B.[Name],
    CASE
        WHEN A.[ID] IS NULL THEN 'Data did not exist before'
        WHEN NOT EXISTS (SELECT A.[Name] INTERSECT SELECT B.[Name]) THEN 'Data has changed'
    END AS [Comment]
FROM TABLENAMEB B
LEFT JOIN #TABLENAMEA A ON B.[ID] = A.[ID]
WHERE 
    A.[ID] IS NULL
    OR NOT EXISTS (SELECT A.[Name] INTERSECT SELECT B.[Name])

The INTERSECT operator correctly handles NULL equality (two NULLs are considered equal here), so this catches all cases where Name values differ—including NULL vs non-NULL scenarios.

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