Find difference between two tables in SQL Server

Question

I have this query that returns 27074 rows from the source table:

SELECT  
    [DOC NO]
    ,[LineNumber]
    ,[SHEET]
    ,[TestPkgNo]
    ,[ITEM CODE]
FROM 
    [excel].[dbo].[MTO2]

And this one (second table )

SELECT  
    DocumentNumber, LineNumber, SheetNumber, PackageNumber, ItemCode
FROM 
    [SPMS2].[dbo].[Materials] 
JOIN
    Lines ON materials.LineId = Lines.Id
JOIN  
    MaterialDescriptions ON MaterialDescriptions.Id = Materials.MaterialDescriptionId
JOIN
    TestPackages ON TestPackages.Id = Materials.TestPackageId

This returns 27032 rows.

I want to find the difference between two tables, I mean the result should be like this :

doc line sheet testpack  sourcetable  secondtable
**   *   *      *          ok           not ok

What's wrong with a simple left join? – Zohar Peled Sep 06 '16 at 05:12 — Zohar Peled, Sep 06 '16 at 05:12
@ZoharPeled Yes it can find the differ. – Ehsan Akbar Sep 06 '16 at 05:14 — Ehsan Akbar, Sep 06 '16 at 05:14
@ZoharPeled how can i make the join on these two tables? – Ehsan Akbar Sep 06 '16 at 05:16 — Ehsan Akbar, Sep 06 '16 at 05:16

score 1 · Accepted Answer · answered Sep 06 '16 at 05:22

This query will return all the records that exist in the source table but doesn't exist in second table.

SELECT  
     [DOC NO]
    ,[LineNumber]
    ,[SHEET]
    ,[TestPkgNo]
    ,[ITEM CODE]
FROM 
    [excel].[dbo].[MTO2] sourcetable

LEFT JOIN
    (
        SELECT  
            DocumentNumber, LineNumber, SheetNumber, PackageNumber, ItemCode
        FROM 
            [SPMS2].[dbo].[Materials] 
        JOIN
            Lines ON materials.LineId = Lines.Id
        JOIN  
            MaterialDescriptions ON MaterialDescriptions.Id = Materials.MaterialDescriptionId
        JOIN
            TestPackages ON TestPackages.Id = Materials.TestPackageId
    ) secondtable
ON sourcetable.[DOC NO] = secondtable.DocumentNumber -- Remove conditions that are not needed
AND sourcetable.[LineNumber] = secondtable.LineNumber -- Remove conditions that are not needed
AND sourcetable.[SHEET] = secondtable.SheetNumber -- Remove conditions that are not needed
AND sourcetable.[TestPkgNo] = secondtable.PackageNumber -- Remove conditions that are not needed
AND sourcetable.[ITEM CODE] = secondtable.ItemCode  -- Remove conditions that are not needed

WHERE secondtable.DocumentNumber IS NULL -- Assuming DocumentNumber is a non-nullable column

Find difference between two tables in SQL Server

1 Answers1