Do you remember Sets in mathematics? Well, our SQL Sets are similar to the Sets in mathematics.

Union, Union all, Intersect and Except are the Set operators in SQL server

What does Set mean?

Set is one of the most fundamental concepts in mathematics. Set is a group of collection of objects.

Relationship among the set can be picturized using a Venn diagram. We will be using Venn diagrams to understand Set operators.

Let’s start with our SQL SET Operators but first I want you to consider the to tables below. This will help us to understand the operators in a better way.

(a). Lunch (b).Dinner

Union

When two or more sets are added they are termed as Union. The result set of union will contain records from both the sets and the records will be distinct.

The SQL UNION is used to combine two or more record set or SQL SELECT statements. It removes the duplicate records (which maybe present in both the record set) and hence returning a distinct result set.

Union operator is used by simply writing UNION keyword between the two SELECT statements

Syntax

Select item from Lunch 
UNION
Select item from Dinner

unionSet

Records from both table after UNION

Above result set shows the 11 records from both the table. What is to be noted that Bread, Coffee and Olives are in both the tables, but the result set contains only single record of Bread, Coffee and Olives and hence there are no duplicate records.

unionVenn

Venn diagram for UNION

The above Venn diagram shows the unification of the tables. The resulted records are from Lunch and Dinner and their common records.

UNION ALL

Union all is a special kind of UNION operator which returns records similar to the UNION, but the records are not distinct

SQL UNION ALL operator is used to combine the result sets of two or more SELECT statements. It does not remove duplicate rows between the various SELECT statements

Syntax

Select item from Lunch 
UNION ALL
Select item from Dinner
unionAllSet
Records from both table after UNION ALL

Above result set shows the 14 records from both the table. Here; Bread, Coffee and Olives are repeated twice as they are present in both the tables. So, UNION ALL returns all the records in both the tables.

Intersection

Intersection between the two or more record sets returns the matching values from the record sets.

The SQL INTERSECT operator is used to return the Distinct results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Syntax

Select item from Lunch 
INTERSECT
Select item from Dinner
intersectSet
Records from both tables after INTERSECT

Result set above shows the Records which are common in both the tables. We can see Bread, Coffee and Olives are returned as the intersection of tables Lunch and Dinner.

The matching records which are returned are Distinct and there are no repeated rows.

intersectVenn

Venn diagram for INTERSECT

Venn diagram shows the colored section between the Record set of Lunch and Dinner which signifies Intersection.

Except

Except in SQL server denotes Complement in Mathematical set. Complement implies that two related sets can be subtracted.

The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset.

The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Syntax

Select item from Lunch 
EXCEPT
Select item from Dinner
exceptSet
Records after applying EXCEPT

The Result set above shows the records which are present in Lunch table but not in Dinner table.

Below is the Except operator relationship represented by Venn diagram. Where colored section denotes records returned by the EXCEPT between the Lunch to that of the Dinner.

exceptVenn

Venn diagram for Lunch EXCEPT Dinner

Important Points to remember:

  • All record sets or the Select statements must have the same number of columns

For e.g

select item, Price, Getdate() as Date from Lunch      
Intersect
select item from Dinner

will raise an error.

  • In every result set the data type of each column must match the data type of its corresponding column in the first result set.
  • In order to sort the result, an ORDER BY clause should be part of the last statement
//WRONG WAY
select item from Lunch  
order by item   --- Wrong way        
Intersect
select item from Dinner   
order by item   --- WRONG way
        
//RIGHT WAY
select item from Lunch  
Intersect
select item from Dinner   
Order by item   --- Right way
  • Aliases for the columns can be used. But for Intersect and except, the first SELECT statement aliases will be used in order by.

For e.g.

select item as D from Lunch  
Intersect
select * from Dinner   
order by D   --- We can’t use order by item
  • Sometime Joins and Set operators act same but they are not exactly same

That’s all with SQL Set operators. If any suggestions or feedback then do let me know in comment section.

Happy coding folks!!