Set Operators in SQL Server

Set operators are combines the 2 or more query result sets and then produces a single result set based on the operator.

There are the following 3 set operators in SQL Server:

1. UNION
2. INTERSECT
3. EXCEPT

It also supports the one multi-set operator UNION ALL.

Each of them are shown in detail with examples.

--CREATE DATABASE
CREATE DATABASE DEMOS

Use demos

Created 2 sample tables.

--CREATE EMP_1 TABLE
CREATE TABLE EMP_1
(
   EMP_ID INT IDENTITY (1, 1),
   NAME VARCHAR (50),
   SALARY INT
)

--CREATE EMP_2 TABLE
CREATE TABLE EMP_2
(
   EMP_ID INT IDENTITY (1, 1),
   NAME VARCHAR (50),
   SALARY INT
)

--INSERT DATA TO BOTH TABLES
Insert data to EMP_1 table
INSERT INTO EMP_1(NAME,SALARY) VALUES('RAKESH',10000)
INSERT INTO EMP_1(NAME,SALARY) VALUES('PANKAJ',20000)
INSERT INTO EMP_1(NAME,SALARY) VALUES('CHITHU',30000)

Insert data into EMP_2 table:

INSERT INTO EMP_2(NAME,SALARY) VALUES('RAVI',20000)
INSERT INTO EMP_2(NAME,SALARY) VALUES('JHANI',15000)
INSERT INTO EMP_2(NAME,SALARY) VALUES('CHITHU',30000)

Check the data.

SELECT * FROM EMP_1
SELECT * FROM EMP_2

Union ALL

It combines two or more query result sets into a single result set.

It doesn't remove the duplicate data. The data is not iin sorted order.



Example

SELECT * FROM EMP_1
UNION ALL
SELECT * FROM EMP_2

Union

It combines two or more query result sets into a single result set.

It does remove the duplicate data. The data is in sorted order.



Example

SELECT * FROM EMP_1
UNION
SELECT * FROM EMP_2

Intersect

Intersect returns the distinct data from both result sets of matched rows only.



Example

SELECT * FROM EMP_1
INTERSECT
SELECT * FROM EMP_2

In the preceding result set are only the returned matched rows from both result sets.

Except

Except returns the distinct rows from the first set only, not the matched rows from the second set.



Example

SELECT * FROM EMP_1
EXCEPT
SELECT * FROM EMP_2

The following are some of the rules for set operators:

 

1. The number of columns for all sets must be equal.
   

   SELECT EMP_ID,NAME,SALARY FROM EMP_1
   UNION
   SELECT EMP_ID,NAME FROM EMP_2

   
   
   
2. The appearance of the column data type order must be compatible.
   

   SELECT EMP_ID,NAME,SALARY FROM EMP_1
   UNION
   SELECT EMP_ID,SALARY,NAME FROM EMP_2

   
   
   
3. In the Except operator <Query1> except <Query2>, <Query2> except <Query1> has results of different outputs.
   
   
   
   
4. <Query 1> union <Query 2> intersect <Query 3>
   
   In the Preceding Query 2 and Query 3 the first combines the result and then Query 1, because of intersect, will be a higher precedence.
   
   If we want to prefer Query 1 and Query 2 as a higher priority then use ( ) symbols.
   
   For example: (<Query 1> union <Query 2>) intersect <Query 3>
   
   
5. In set operators NULL is also handled, but joins don't handle nulls.
   

   CREATE TABLE #SAMPLE_1(ID INT NULL)
   CREATE TABLE #SAMPLE_2(ID INT NULL)
   
   INSERT INTO #SAMPLE_1 VALUES(1),(NULL)
   INSERT INTO #SAMPLE_2 VALUES(1),(NULL)
   
   SELECT * FROM #SAMPLE_1
   UNION
   SELECT * FROM #SAMPLE_2
   
   SELECT * FROM #SAMPLE_1 AS S1 JOIN #SAMPLE_2 AS S2 ON S1.ID=S2.ID

   
   
   
6. Order by can only exist in the last query set.
   

   SELECT * FROM EMP_1
   ORDER BY SALARY
   UNION
   SELECT * FROM EMP_2
   ORDER BY SALARY
   
   SELECT * FROM EMP_1
   UNION
   SELECT * FROM EMP_2
   ORDER BY SALARY