SQL Set operators

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.

select SalesPersonID, SalesQuota from Sales.SalesPersonQuotaHistory
UNION
select SalesPersonID, SalesQuota from Sales.SalesPerson

Union All operator

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

select SalesPersonID, SalesQuota from Sales.SalesPersonQuotaHistory
UNION ALL
select SalesPersonID, SalesQuota from Sales.SalesPerson

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.

select SalesPersonID from Sales.SalesPersonQuotaHistory
INTERSECT
select SalesPersonID from Sales.SalesPerson

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.

select SalesPersonID from Sales.SalesPersonQuotaHistory

MINUS
select SalesPersonID from Sales.SalesPerson

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.