Post
Ask Question

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.

  1. --CREATE DATABASE  
  2. CREATE DATABASE DEMOS  
Use demos

Created 2 sample tables.
  1. --CREATE EMP_1 TABLE  
  2. CREATE TABLE EMP_1  
  3. (  
  4.    EMP_ID INT IDENTITY (1, 1),  
  5.    NAME VARCHAR (50),  
  6.    SALARY INT  
  7. )  
  8.   
  9. --CREATE EMP_2 TABLE  
  10. CREATE TABLE EMP_2  
  11. (  
  12.    EMP_ID INT IDENTITY (1, 1),  
  13.    NAME VARCHAR (50),  
  14.    SALARY INT  
  15. )  
  16.   
  17. --INSERT DATA TO BOTH TABLES  
  18. Insert data to EMP_1 table  
  19. INSERT INTO EMP_1(NAME,SALARY) VALUES('RAKESH',10000)  
  20. INSERT INTO EMP_1(NAME,SALARY) VALUES('PANKAJ',20000)  
  21. INSERT INTO EMP_1(NAME,SALARY) VALUES('CHITHU',30000)  
Insert data into EMP_2 table:
  1. INSERT INTO EMP_2(NAME,SALARY) VALUES('RAVI',20000)  
  2. INSERT INTO EMP_2(NAME,SALARY) VALUES('JHANI',15000)  
  3. INSERT INTO EMP_2(NAME,SALARY) VALUES('CHITHU',30000)  
Check the data.
  1. SELECT * FROM EMP_1  
  2. SELECT * FROM EMP_2  
execute

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.

Union ALL

Example
  1. SELECT * FROM EMP_1  
  2. UNION ALL  
  3. SELECT * FROM EMP_2  
data is in not sorted order

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.

Union

Example
  1. SELECT * FROM EMP_1  
  2. UNION   
  3. SELECT * FROM EMP_2  
sorted order

Intersect

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

Intersect

Example
  1. SELECT * FROM EMP_1  
  2. INTERSECT  
  3. SELECT * FROM EMP_2  
see result

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.

Except

Example
  1. SELECT * FROM EMP_1  
  2. EXCEPT  
  3. SELECT * FROM EMP_2  
emp

The following are some of the rules for set operators:

 

  1. The number of columns for all sets must be equal.
    1. SELECT EMP_ID,NAME,SALARY FROM EMP_1  
    2. UNION  
    3. SELECT EMP_ID,NAME FROM EMP_2  
    set operators

  2. The appearance of the column data type order must be compatible.
    1. SELECT EMP_ID,NAME,SALARY FROM EMP_1  
    2. UNION  
    3. SELECT EMP_ID,SALARY,NAME FROM EMP_2  
    query output

  3. In the Except operator <Query1> except <Query2>, <Query2> except <Query1> has results of different outputs.

    results out

  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.
    1. CREATE TABLE #SAMPLE_1(ID INT NULL)  
    2. CREATE TABLE #SAMPLE_2(ID INT NULL)  
    3.   
    4. INSERT INTO #SAMPLE_1 VALUES(1),(NULL)  
    5. INSERT INTO #SAMPLE_2 VALUES(1),(NULL)  
    6.   
    7. SELECT * FROM #SAMPLE_1  
    8. UNION   
    9. SELECT * FROM #SAMPLE_2  
    10.   
    11. SELECT * FROM #SAMPLE_1 AS S1 JOIN #SAMPLE_2 AS S2 ON S1.ID=S2.ID  
    result

  6. Order by can only exist in the last query set.
    1. SELECT * FROM EMP_1  
    2. ORDER BY SALARY  
    3. UNION   
    4. SELECT * FROM EMP_2  
    5. ORDER BY SALARY  
    6.   
    7. SELECT * FROM EMP_1  
    8. UNION   
    9. SELECT * FROM EMP_2  
    10. ORDER BY SALARY  
    last query set


Similar Articles