SQL Server：编写函数检测两表同名关联列的数据差异

Got it, let's break down how to solve this problem. You need to identify two types of differences between CompanyRecords and JointCompanies (linked by CompanyId):

1. CompanyIds that exist in one table but not the other
2. Mismatched field values (CompanyName/ProcessID) for shared CompanyIds

First, let's confirm the table structures you provided (note the typo in JointCompanies' process ID column: ComanyProcessID instead of CompanyProcessID—I'll use that spelling in the queries below):

1. Find CompanyIds Present in Only One Table #

Use a FULL OUTER JOIN to combine both tables, then filter for rows where one side has no matching CompanyId:

-- Identify CompanyIds unique to one table
SELECT
    COALESCE(cr.CompanyId, jc.CompanyId) AS CompanyId,
    CASE
        WHEN cr.CompanyId IS NULL THEN 'Only exists in JointCompanies'
        ELSE 'Only exists in CompanyRecords'
    END AS Status
FROM CompanyRecords cr
FULL OUTER JOIN JointCompanies jc 
    ON cr.CompanyId = jc.CompanyId
WHERE cr.CompanyId IS NULL OR jc.CompanyId IS NULL;

For your sample data, this will return:

• 333 (only in CompanyRecords)
• 444 (only in JointCompanies)

2. Find Mismatched Values for Shared CompanyIds #

Use an INNER JOIN to focus on CompanyIds present in both tables, then compare the relevant fields:

-- Identify field value mismatches for shared CompanyIds
SELECT
    cr.CompanyId,
    cr.CompanyName AS [CompanyName (CompanyRecords)],
    jc.CompanyName AS [CompanyName (JointCompanies)],
    cr.CompanyProcessID AS [ProcessID (CompanyRecords)],
    jc.ComanyProcessID AS [ProcessID (JointCompanies)]
FROM CompanyRecords cr
INNER JOIN JointCompanies jc 
    ON cr.CompanyId = jc.CompanyId
WHERE
    cr.CompanyName <> jc.CompanyName
    OR cr.CompanyProcessID <> jc.ComanyProcessID;

For your sample data, this will return CompanyId 222 (since CompanyName differs: Sears vs KMart).

3. Wrap It All Into a Reusable Function #

If you want a single tool to get all differences at once, you can create a SQL function that combines both queries:

CREATE FUNCTION dbo.CompareCompanyTables()
RETURNS TABLE
AS
RETURN
(
    -- Presence mismatches
    SELECT
        COALESCE(cr.CompanyId, jc.CompanyId) AS CompanyId,
        'Presence Mismatch' AS DifferenceType,
        CASE
            WHEN cr.CompanyId IS NULL THEN 'Only in JointCompanies'
            ELSE 'Only in CompanyRecords'
        END AS Details,
        NULL AS [CompanyName (CR)],
        NULL AS [CompanyName (JC)],
        NULL AS [ProcessID (CR)],
        NULL AS [ProcessID (JC)]
    FROM CompanyRecords cr
    FULL OUTER JOIN JointCompanies jc 
        ON cr.CompanyId = jc.CompanyId
    WHERE cr.CompanyId IS NULL OR jc.CompanyId IS NULL

    UNION ALL

    -- Value mismatches
    SELECT
        cr.CompanyId,
        'Value Mismatch' AS DifferenceType,
        CASE
            WHEN cr.CompanyName <> jc.CompanyName THEN 'CompanyName differs'
            ELSE 'ProcessID differs'
        END AS Details,
        cr.CompanyName AS [CompanyName (CR)],
        jc.CompanyName AS [CompanyName (JC)],
        cr.CompanyProcessID AS [ProcessID (CR)],
        jc.ComanyProcessID AS [ProcessID (JC)]
    FROM CompanyRecords cr
    INNER JOIN JointCompanies jc 
        ON cr.CompanyId = jc.CompanyId
    WHERE
        cr.CompanyName <> jc.CompanyName
        OR cr.CompanyProcessID <> jc.ComanyProcessID
);

To use it, just run:

SELECT * FROM dbo.CompareCompanyTables();

Quick Note #

Double-check that ComanyProcessID in JointCompanies is intentional (it's missing a 'p'). If it's a typo, update the column name and adjust the queries to match CompanyProcessID for consistency.

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