What is Set operators? Types of Set Operators? Union, Intersect, Except in SQL Server with example

Welcome to “Freshers Junction SQL Server Technical interview Questions Section”. Today we are discussing About Set Operators in SQL Server with example.

We all know, Set operators in SQL Server with example? Is the very important technical interview question for both freshers and experience candidates. So now we discuss in detailed about set operators such as what are set operators, types of set operators, how set operators works.

What is set Operators?

                Set operators combines the data from two tables into one table. Set operators queries are called as “Compound Queries”

Types of Set Operators?

In sql server there are 3 types of set operators. Those are Intersect set Operator, Except set operator and Union set Operator.

Before going to explain each operator with example sql query, we need to create two sql server tables in SQL Server Management Studio.

How to Create Table in SQL Server management studio

The two table names which we create are Employee99 and Employee98. These two tables are as shown in below image.

Now we see how each operator works.

1. Union Set Operator:

We all know how union Set operator works in mathematics. As same union set operator works here also. What I mean is ” Union Set Operator  adds unique records from both the tables”.

Example:

  Select dept from employee99
   Union
  Select dno from employee98

Write the above query in SQL Server Management Studio and execute.

The output of union set operator is as follow.

1. 10
2. 20
3. 30
4. 40
5. 50
6. 60

SQL Query To Get Top 2 Highest Salary Employee Details In a SQL Server Table

2. Intersect Set Operator

We all know how Intersect Set operator works in mathematics. As same Intersect set operator works here also. What I mean is ” Intersect Set Operator takes only matched records from both the tables”.

Example:

  Select dept from employee99
   intersect
  Select dno from employee98

Write the above query in SQL Server Management Studio and execute.

The output of intersect set operator is as follow.

1. 10
2. 20
3. 30
4. 40

SQL Query to get Department wise Maximum Salary Employee Details from SQL server Table

3.  Except set operator:

                This Excepts set operator is also works as Mathematical set operator. “Except set operator gives all records from first table except matched records from second table”.

That is First table – Second Table

Example:

  Select dept from employee99
   except
  Select dno from employee98

Write the above query in SQL Server Management Studio and execute.

The output of except set operator is as follow.

6.60

You may Also Like:

SQL Query to Replace a character with Some other character in a column

What is the difference between Where clause and having clause in SQL Server

Note: Press Ctrl+D to bookmark this page and remind once again if you needed.

If you have any more Queries about this topic, Then comment here we are ready to help you at any time.

FreshersJunction also provides Jobs Information, Software Developer Registration links, Technical Interview Questions, Tricks and tips to solve Aptitude problems.

You may also search about set operators in google as:
Set operators in sql server, different types of Set operators in sql server 2008 with example, intersect operator with example in sql server, Set operators in sql server with example, union, intersect, except set operators in sql server with example, difference between union, intersect, except set operators, sql server set operators, types of set operators, what is set operators, sql server technical interview on set operators