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 LunchUNIONSelect item from Dinner

*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.

*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 LunchUNION ALLSelect item from Dinner

###### 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 LunchINTERSECTSelect item from Dinner

*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.

*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 LunchEXCEPTSelect item from Dinner

###### 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.

*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 LunchIntersectselect 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 WAYselect item from Lunchorder by item --- Wrong wayIntersectselect item from Dinnerorder by item --- WRONG way//RIGHT WAYselect item from LunchIntersectselect item from DinnerOrder 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 LunchIntersectselect * fromDinnerorder by D --- We can’t use order by item

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

*Happy coding folks!!*

I was aware of only JOINs in sql. UNION and INTERSECT are completely new to me. Thanks for introducing me these new operations.

LikeLike

Glad to hear that. Will post more on Set operators and Joins in future.

LikeLike