T-SQL difference between WHERE EXISTS and INTERSECT

Question

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.

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

Answer 1

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

  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.

And if you run

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

You will get 1 record.

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

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