存在和交叉之间的T-SQL差异

Can you tell me what is the difference between these two? I have tried using both for comparing two tables, but the results are a bit different:

你能告诉我这两者有什么区别吗?我试过用这两种方法来比较两个表，但是结果有点不同:

  SELECT a.CustID FROM tbl1 AS a 
  WHERE EXISTS (SELECT b.CustID FROM tbl2 b WHERE (a.CustID=b.CustID))

This query results 178 rows. However, the query below results in 162 rows.

此查询结果为178行。但是，下面的查询结果是162行。

  SELECT a.CustID FROM tbl1 AS a
  INTERSECT
  SELECT b.CustID FROM tbl2 AS b

1 个解决方案

#1

Following query will result multiple rows if you have duplicate CustId in tbl1

如果在tbl1中有重复的CustId，则后续查询将产生多个行

  SELECT a.CustID FROM tbl1 AS a 
  WHERE EXISTS (SELECT b.CustID FROM tbl2 b WHERE (a.CustID=b.CustID))

In case if you use INTERSECT, duplicates will be eliminated

如果你使用交叉，重复将被消除。

  SELECT a.CustID FROM tbl1 AS a
  INTERSECT
  SELECT b.CustID FROM tbl2 AS b

For example.

为例。

CREATE TABLE tbl1 (CustId int, CustName varchar(100))
GO
INSERT INTO tbl1 VALUES(1 ,'N1')
GO
INSERT INTO tbl1 VALUES(2 ,'N2')
GO
INSERT INTO tbl1 VALUES(1 ,'N3')
GO
CREATE TABLE tbl2 (CustId int, CustName varchar(100))
GO
INSERT INTO tbl2 VALUES (1 ,'N1')
GO

For the above tables, if you run

对于上面的表，如果您运行

 SELECT a.CustID FROM tbl1 AS a 
  WHERE EXISTS (SELECT b.CustID FROM tbl2 b WHERE (a.CustID=b.CustID))

You will get 2 records.

你会得到2张唱片。

And if you run

如果你运行

   SELECT a.CustID FROM tbl1 AS a
  INTERSECT
  SELECT b.CustID FROM tbl2 AS b

You will get 1 record.

你会得到1张唱片。

Note : If you use DISTINCT with the query 1, both will result in same output.

注意:如果在查询1中使用了DISTINCT，这两个结果都将导致相同的输出。

  SELECT DISTINCT a.CustID FROM tbl1 AS a 
  WHERE EXISTS (SELECT b.CustID FROM tbl2 b WHERE (a.CustID=b.CustID))

#1