DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find non-matching rows in two tables?

The following Microsoft SQL Server T-SQL code examples demonstrate how to find non-matching rows in two tables with FULL OUTER JOIN or the EXCEPT operator:

 

-- SELECT INTO create 2 test tables

USE tempdb;

SELECT   TOP ( 400 ) ProductID,

                     ProductName = Name,

                     ListPrice,

                     StandardCost

INTO     Product1

FROM     AdventureWorks2008.Production.Product

ORDER BY NEWID()

-- (400 row(s) affected)

 

SELECT   TOP ( 400 ) ProductID,

                     ProductName = Name,

                     ListPrice,

                     StandardCost

INTO     Product2

FROM     AdventureWorks2008.Production.Product

ORDER BY NEWID()

-- (400 row(s) affected)

 

-- SQL FULL OUTER JOIN method

SELECT A.ProductID,

       A.ProductName,

       A.ListPrice,

       A.StandardCost,

       B.ProductID,

       B.ProductName,

       B.ListPrice,

       B.StandardCost

FROM   Product1 A

       FULL OUTER JOIN Product2 B

         ON A.ProductID = B.ProductID

            AND A.ProductName = B.ProductName

            AND A.ListPRice = B.ListPrice

            AND A.StandardCost = B.StandardCost

WHERE  A.ProductID IS NULL

        OR B.ProductID IS NULL

 

GO

-- (158 row(s) affected)

 

-- SQL EXCEPT operator method

(SELECT MESSAGE = 'Not in 2',

        *

 FROM   Product1

 EXCEPT

 SELECT MESSAGE = 'Not in 2',

        *

 FROM   Product2)

UNION ALL

(SELECT MESSAGE = 'Not in 1',

        *

 FROM   Product2

 EXCEPT

 SELECT MESSAGE = 'Not in 1',

        *

 FROM   Product1)

GO

-- (158 row(s) affected)

/*

Message     ProductID   ProductName ListPrice

....

Not in 2    321   Chainring Nut     0.00

Not in 2    343   Flat Washer 2     0.00

Not in 2    356   LL Grip Tape      0.00

Not in 2    357   ML Grip Tape      0.00

....

*/

DROP TABLE Product1

DROP TABLE Product2

 

Related article:

 

Using Outer Joins

 

http://www.sqlusa.com/bestpractices2005/fulljoin/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE