How to compare two tables for differences?

Execute the following Microsoft SQL Server 2008 T-SQL scripts in Query Editor to demonstrate the comparison of two tables for differences in rows and/or columns (cells).

------------

-- SQL SERVER COMPARE 2 TABLES FOR ROW & COLUMN DIFFERENCES

------------
-- TEMPLATE - SQL Server T-SQL compare two tables
SELECT Label='Found IN Table1, NOT IN Table2',* FROM 
(SELECT * FROM Table1
 EXCEPT
 SELECT  * FROM Table2) x
UNION ALL
SELECT Label='Found IN Table2, NOT IN Table1',* FROM
(SELECT  * FROM Table2
 EXCEPT
 SELECT * FROM Table1) y

GO

------------

-- SQL Server T-SQL compare Product tables for 2005 & 2008
SELECT Label='Found IN AW8PP, NOT IN AWPP',* FROM 
(SELECT * FROM AdventureWorks2008.Production.Product
 EXCEPT
 SELECT  * FROM AdventureWorks.Production.Product) x
UNION ALL
SELECT Label='Found IN AWPP, NOT IN AW8PP',* FROM
(SELECT  * FROM AdventureWorks.Production.Product
 EXCEPT
 SELECT * FROM AdventureWorks2008.Production.Product) y
GO

-- The two tables are identical
-- (0 row(s) affected
------------

-- SQL find rows present in both tables
SELECT * FROM AdventureWorks2008.Production.Product
INTERSECT
SELECT * FROM AdventureWorks.Production.Product
-- (504 row(s) affected)
------------

------------
-- ALL SQL Server T-SQL versions
------------
-- SQL finding rows missing in source table based on PRIMARY KEY JOIN to target table 
USE tempdb;
SELECT * INTO Product1 FROM Northwind.dbo.Products
SELECT * INTO Product2 FROM Northwind.dbo.Products
DELETE Product2 WHERE UnitPrice > 100.0
-- (2 row(s) affected)
 
SELECT * FROM Product1 p1
WHERE NOT EXISTS (SELECT * FROM Product2  p2 
                  WHERE p2.ProductId = p1.ProductId) 
GO
/* Partial results
ProductID   ProductName
29    Thüringer Rostbratwurst
38    Côte de Blaye
*/
 
-- Alternate  query - same results
SELECT * FROM Product1  p1 
  LEFT OUTER JOIN Product2  p2 
    ON p2.ProductId = p1.ProductId 
WHERE p2.ProductId IS NULL 
GO
-- (2 row(s) affected)
DROP TABLE tempdb.dbo.Product1
DROP TABLE tempdb.dbo.Product2

------------

Related articles:

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Comparison Query to Compare Two SQL Server Tables