TSQL Tip of the Day

Today we will look at use of INTERSECT and EXCEPT.

• EXCEPT - Data exists in one table and does not exist in another table. In SET operations it is represented as A-B.
• INTERSECT - Data exists in both the table. Intersection in SET Operations.

Let's try an example. Step 1 - Creating Test Tables

 --STEP 1

 IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
 CREATE TABLE TestTable1
 (
 Number INT IDENTITY(1,1) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )
 GO
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
 CREATE TABLE TestTable2
 (
 Number INT IDENTITY(10,5) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )

Step 2 - Populate Test Data
--STEP 2
--Insert Records into this table
 DECLARE @I INT
 SET @I = 1
 WHILE 1 = 1
 BEGIN
 SET @I = @I + 1
 INSERT INTO TestTable1(A,B,C,D)
 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))

 INSERT INTO TestTable2(A,B,C,D)

 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
  IF @I=1000
 BREAK;
 END

Step 3 - EXCEPT Example
--Number which is present in TestTable1 and not in TestTable2
SELECT Number
FROM dbo.TestTable1
EXCEPT
SELECT Number
FROM dbo.TestTable2

--Multiples of 5 excluded in results
--Alternate query

SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE NOT EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

Step 4 - INTERSECT Example 

--Number common in both TestTable1 and TestTable2
SELECT Number
FROM dbo.TestTable1
INTERSECT
SELECT Number
FROM dbo.TestTable2

--Alternate query
SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

--Multiples of 5 listed in results

From MSDN - Reference

Happy Reading!!!