SQL how to find distinct values between two tables?-CodePudding

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?

CodePudding user response：

select distinct column1
from (
    select col1 from table1
    union all
    select col2 from table2
) a

You might to have union all.

CodePudding user response：

yes,it is easy by sub-query.

SELECT [HORSEPOWER] FROM [dbo].[Auct_Car_ex] WHERE [HORSEPOWER] NOT IN(
   SELECT [Horsepower] FROM [dbo].[Auct_Car] GROUP BY [Horsepower]
)
GROUP BY [HORSEPOWER]