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.

More Articles

 

Share your thought



PLEASE DISABLE ADBLOCKER AND SUPPORT US!

Please support us by enabling ads on this page. Refresh

YOU DON'T LIKE ADS, WE ALSO DON'T LIKE ADS!
 
But we have to show ads on our site to keep it free and updated. We have to pay huge server costs, domain costs, CDN Costs, Developer Costs, Electricity and Internet Bill. Your little contribution will encourage us to regularly update this site.