|
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/
|
