Home / Database / Union , Intersect and Except Set Operators
operator

Union , Intersect and Except Set Operators

Set Operators :

Definition : Set operators are useful in performing the mathematical operations on the data present in the table.

There must be at least two select statement so that the operations can be performed on the data by comparing the data from each select statement.

Rules to be Satisfied to use Set operators :

  1. In all sets(i.e. Select statements) the number of columns selected must be same.
  2. Datatype of the corresponding columns must be same.

For understanding above rules consider the Student table as follow:

operat Union , Intersect  and Except Set Operators Database SQL Server

There are two sets

  1. SELECT * FROM STUDENT WHERE DEPARTMENT= “COMPUTER”

Result :

1 Union , Intersect  and Except Set Operators Database SQL Server

  1. SELECT * FROM STUDENT WHERE DEPARTMENT= “MECHANICAL”

2 Union , Intersect  and Except Set Operators Database SQL Server
Both sets returns same number of columns i.e. ‘Student Name’ & ‘Department’. The datatype of these tables columns are also same.

In SQL Server 2008, following set operators are present.

  1. Union

This set operator is used to merge the data from two sets(select statements) & display the result with distinct values.

E.g. Consider the example where we have to find the Student from the “COMPUTER”  & “MECHANICAL” department, query will be as follows.

Query :

SELECT * FROM  dbo.STUDENT WHERE DEPARTMENT= “COMPUTER”

UNION

SELECT * FROM  dbo.STUDENT WHERE DEPARTMENT= “MECHANICAL”

Result :

3 Union , Intersect  and Except Set Operators Database SQL Server

Union All

We can use the ‘All‘ keyword with the ‘Union‘ set operator. ‘All‘ operator will return the result of two sets with duplicates value included. It means, when we need duplicate entries in the union of two set operators we will use the ‘All‘ keyword.

Query :

SELECT * FROM  dbo.STUDENT WHERE DEPARTMENT= “COMPUTER”

UNION ALL

SELECT * FROM  dbo.STUDENT WHERE DEPARTMENT= “MECHANICAL”

  1. Intersect

Intersect set operator returns only the common entries present in the set operators column.

Query :

SELECT [Student Name] FROM  dbo.STUDENT WHERE DEPARTMENT= ‘Computer’

INTERSECT

SELECT [Student Name] FROM  dbo.STUDENT WHERE DEPARTMENT= ‘Mechanical’

Result :

4 Union , Intersect  and Except Set Operators Database SQL Server

  1. Except

This set operator is used to get the values of first set which are not present in 2nd set.

Query :

SELECT [Student Name] FROM  dbo.STUDENT WHERE DEPARTMENT= ‘Computer’

EXCEPT

SELECT [Student Name] FROM  dbo.STUDENT WHERE DEPARTMENT= ‘Mechanical’

Result :

5 Union , Intersect  and Except Set Operators Database SQL Server

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Delete Command

Difference between Delete and Truncate command

Difference between Delete and Truncate command Delete Command 1. It allows to delete all rows as ...

One comment

Leave a Reply