SET OPERATORS

            Set operators are used to combine two or more data sets into single data-set.

·        Union/Union All

·        Except

·        Intersect

Union/Union All:

Union or Union all set operators are used to combine tow or more similar data sets (same number of columns, column order, and similar data types).

Note: If data sets are having duplicates or same records,

“UNION” eliminates duplicate rows from the combined result set.

“UNION ALL” includes duplicate rows from the combined result set.

Example: Union1 and Union2 are tow tables with same number of columns and data types.

Table creation script:

Table UNION1:

      CREATE TABLE UNION1

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO UNION1 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO UNION1 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

      INSERT INTO UNION1 VALUES (104,'Dama','Puja','F','Manager',110,20,8000,NULL,'2009-05-19')

Table UNION2:

      CREATE TABLE UNION2

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO UNION2 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO UNION2 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO UNION2 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

·        Combine both the data sets into single dataset without duplicates.

SELECT * FROM UNION1

UNION

SELECT * FROM UNION2

·        Combine the data sets into single data-set with entire data (including duplicates).

SELECT * FROM UNION1

UNION ALL

SELECT * FROM UNION2

Except: EXCEPT returns any distinct values from the left query and that value should not be in right query.

Example:

SELECT * FROM UNION1

EXCEPT

SELECT * FROM UNION2

Intersect: INTERSECT returns any distinct values that are returned by both the query.

Example:

SELECT * FROM UNION1

INTERSECT

SELECT * FROM UNION2