Set operations in Sql server

 SQL Server set operation

set operation allows us combine or select common data from more 2 tables/ queries. If you want to get data from more than two tables then you will have to repeat set operator between each query.

There are basically 3 types of set operations like 

1. Union 
2. Union all (special case A + B)
3. Intersection
4. Except (Minus) 

For demo we will use the below query to create a table and use it throughout out examples

CREATE TABLE Product1 (ProductID INT, ProductName VARCHAR(15)) 

CREATE TABLE Product2 (ProductID INT, ProductName VARCHAR(15)) 

INSERT INTO Product1 VALUES (1,'Pen'),(3,'Notebook'),(4,'Pin'),(6,'Punch'),(7,'Glue')

INSERT INTO Product2 VALUES (1,'Pen'),(2,'Stapler'),(4,'Pin'),(8,'Book'),(9,'Glitter')

UNION

union allows us to combine data from two different queries/tables. If the data is common between 1st and 2nd query then it gives distinct data. The output of union is sorted. 

A = {1,2,3,6}, B = {2,3,4}

UNION - A U B = {1,2,3,4,6}

Example

SELECT ProductID, ProductName FROM Product1

UNION

SELECT ProductID, ProductName FROM Product1

UNION ALL

union all is slightly different than union where data from 2nd query directly gets appended below the first query. Union is always faster than union operation as the data is simple appended and not distinct or sorting operation is done. 

SELECT ProductID, ProductName FROM Product1

UNION ALL

SELECT ProductID, ProductName FROM Product1

INTERSECT

Intersect gives you the common data between two queries. 

A = {1,3,5}

B = {1,5,7}

A ∩ B = {1,5}

SELECT ProductID, ProductName FROM Product1

INTERSECT

SELECT ProductID, ProductName FROM Product1

EXCEPT (MINUS)

This operator gives the result from first query which are not present in 2nd query 

A = {1,3,5}

B = {1,2,5}

A - B ={3}

SELECT ProductID, ProductName FROM Product1

EXCEPT

SELECT ProductID, ProductName FROM Product1

Considerations of set operations

While applying set operator make sure the data type of the column position between the query must be same. for e.g in above queries ProductID in query 1 and 2 at position 1 are both INT and ProductName is in position 2 with data type as VARCHAR. Since set operation allows columns of same data types as same position, make sure that you places columns in correct position, for instance you should not swap two varchar field else you might get wrong result.

 although the query will compile correctly.

Common questions which you can get from set operations are below. 

• Difference between union and union all
• Get list of products which were sold in july-2021 but not in june-2021.
• Get list of product which were sold both in june-2021 and july-2021