SQL Set operators

In this chapter you will learn:
What is Set operator?
Types of Set operator
What is union operator?
What is union all operator?
What is intersect operator?
What is minus operator?

What is Set operator?

Set operator is used to combine two or more rows or columns of table of database. There are a few set operators that are supported in sql. The set operators are generally used for get data/records from database in different special conditions. Such as union, intersect, distinct etc.

Types of Set operator

Union operator

Union is a type of set operator that is used to combine the results of two or more than two select statements. It not shows the duplicate rows.

Example
select SalesPersonID, SalesQuota from Sales.SalesPersonQuotaHistory
UNION
select SalesPersonID, SalesQuota from Sales.SalesPerson
Output Union operator example
Union All operator

The union all operator works like union operator but the different is that, it shows the duplicate rows.

Example
select SalesPersonID, SalesQuota from Sales.SalesPersonQuotaHistory
UNION ALL
select SalesPersonID, SalesQuota from Sales.SalesPerson
Output union all operator example
Intersect Operator

The intersect operator is also used for combine two or more than two select statement but it display only the common value of the select statement.

Example

select SalesPersonID from Sales.SalesPersonQuotaHistory
INTERSECT
select SalesPersonID from Sales.SalesPerson
Output intersect operator example
Minus Operator

Minus operator is used to display first statement value but it also not display the value that matched from the second statement result.

Example

select SalesPersonID from Sales.SalesPersonQuotaHistory

MINUS
select SalesPersonID from Sales.SalesPerson
Output minus operator example

SUMMARY

In this chapter we have learned about all types of set operators that are used in sql just like union, union all, intersect, etc. In the next chapter we will study about comparison operater and its type.

 

Share your thought