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:
- UNION
- INTERSECT
- EXCEPT
It also supports the one multi-set operator UNION ALL.
Each of them are shown in detail with examples.
Use demosCreated 2 sample tables.
-
- CREATE TABLE EMP_1
- (
- EMP_ID INT IDENTITY (1, 1),
- NAME VARCHAR (50),
- SALARY INT
- )
-
-
- CREATE TABLE EMP_2
- (
- EMP_ID INT IDENTITY (1, 1),
- NAME VARCHAR (50),
- SALARY INT
- )
-
-
- 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:
- 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
- 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
- In the Except operator <Query1> except <Query2>, <Query2> except <Query1> has results of different outputs.
- <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>
- 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
- 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