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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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