SQL如何在两个表之间找到不同的值?

Question

有一个内部表和一个外部表。内部表实际上是外部表的副本，其中一些字段重命名，它们大致相同。由于某些原因，由于操作不当，内部表中的数据可能与外部表不匹配。情况如下:

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

它们有相同的行数。

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

但是不同Horsepower的个数不同。我想知道HORSEPOWER的哪个值在Auct_Car_ex中存在，而在Auct_Car中不存在。我怎样才能找到它?

Answer 1

直接用EXCEPT

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

Answer 2

是，通过子查询很容易。

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

Answer 3

这看起来像是不存在

select horsepower
from Auct_Car_ex x
where not exists (
select * from Auct_Car a
where a.horsepower = x.horsepower
);