SQL how to find distinct values between two tables?

Question

We have an internal and an external table. The internal table is actually a copy of the external table with some fields renamed and they are roughly the same. For some reason, the data in the internal table might not match the external table because of inappropriate operation. Here is the case:

SELECT COUNT(*) AS [Total Rows] 
FROM [dbo].[Auct_Car_Ex];
-- (ANS.) 76716

SELECT COUNT(*) AS [Total Rows] 
FROM [dbo].[Auct_Car];
-- (ANS.) 76716

They have the same number of rows.

SELECT COUNT(DISTINCT([HORSEPOWER])) 
FROM [dbo].[Auct_Car_ex];
-- (ANS.) 459

SELECT COUNT(DISTINCT([Horsepower])) 
FROM [dbo].[Auct_Car];
-- (ANS.) 458

However, the number of distinct Horsepower is different. I'd like to know which value of HORSEPOWER exists in Auct_Car_ex but not in Auct_Car. How can I find it?

Answer 1

Just use EXCEPT

SELECT acx.HORSEPOWER
FROM dbo.Auct_Car_ex acx
EXCEPT
SELECT ac.Horsepower
FROM dbo.Auct_Car ac;