Set Operation in SQL

Set Operation in SQL:

The set operations in SQL are used to combine the results of more than two SELECT statements. The set operations convert the result of two queries into a single query.

And the queries that are using the set operations are also called compound queries. The SQL set operations are also used to combine the rows of using some conditions from different queries. The set operations result the data that meet certain specific conditions. Following are some of the set operations used in SQL:

 

Union

The union set operation is used to combine the result of two select statements. The union set operation removes the duplicated rows from the result set.

The data types of the columns of the two tables and the number of columns of the tables should be the same to apply the union set operation. Following is the pictorial representation of the Union set operation:

 

EXAMPLE of Union Set Operation:

Suppose you have the following two tables Instructor and Student:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
80	Kathy

Instructor

Dept_No	NAME
10	John Abraham
20	Tim
30	Ali
40	Faddy
60	James
70	Sam

Student

Use the following query to understand the concept of Union set operation:

Select * from Instructor

UNION

Select * from Student

This query will display the duplicated result one time that is in the above tables some of the entries in the Dept_No column are the same but the union function will display them once. The result generated by the above query is as follows:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
60	James
70	Sam
80	Kathy

 

Union All

The union all set operation is similar to the Union set operation but the only difference is that it shows the duplicated rows as well. The pictorial representation of the union all set operation is given below:

Consider the following example to understand the concept of union all operation:

 

EXAMPLE of Union All Set Operation

Suppose you have the following two tables Instructor and Student:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
80	Kathy

Instructor

Dept_No	NAME
10	John Abraham
20	Tim
30	Ali
40	Faddy
60	James
70	Sam

Student

Use the following query for the union all set operation:

Select * from Instructor

Union all

Select * from Student

The above query will display all the data in both of the tables whether they are duplicated or not. The following result will be generated by the above query:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
80	Kathy
10	John Abraham
20	Tim
30	Ali
40	Faddy
60	James
70	Sam

Intersect

The Intersect set operation is used to combine the result of two select statements. The Intersect set operation returns that records that are common on both of the tables or select statement.

The Intersect set operation includes all the duplicated rows from the result set. The data types of the columns of the two tables and the number of columns of the tables should be the same to apply the Intersect set operation. Following is the pictorial representation of the Intersect set operation:

 

EXAMPLE of Intersect Set Operation

Suppose you have the following two tables Instructor and Student:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
80	Kathy

Instructor

Dept_No	NAME
10	John Abraham
20	Tim
30	Ali
40	Faddy
60	James
70	Sam

Student

To understand the use and concept of the Intersect operation use the following query:

Select * from Instructor

Intersect

Select * from Student

The above query will display all the data that is common in both of the tables. The following result will be generated by the above query:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy

 

Minus Set Operation

The minus set operation is used to return those rows that are in the first select statement or table and they are not returned by the second table or second select statement. The pictorial representation of the minus operation is given below:

EXAMPLE of Minus Set Operation

Suppose you have the following two tables Instructor and Student:

Dept_No	Name
10	John Abraham
20	Tim
30	Ali
40	Faddy
50	Kate
80	Kathy

Instructor

Dept_No	NAME
10	John Abraham
20	Tim
30	Ali
40	Faddy
60	James
70	Sam

Student

To understand the use and concept of the minus set operation use the following query:

Select * from Instructor

MINUS

Select * from Student

The above query will display all the data that is common in both of the tables. The following result will be generated by the above query:

Dept_No	Name
50	Kate
80	Kathy

It can be seen in the above example that only those rows are returned that are in the first select statement or first table and do not exist in the second select statement or second table.

 

Set Operation rules

The following are some of the rules that should be followed when using the set operations:

 

Sorting Results of Compound Queries

If the user wants to sort the results obtained by compound queries then he may use the order by clause. The order by clause is used after the last query. When the column names are specified in an order by clause then you have to choose the column names of those that appear in the first query of the given compound query.

You should also keep the column names of both of the queries to avoid errors when sorting. That is if the column names in the second query are different from the column names in the first query and the user is using the column names of second query with the order by clause then an error will occur.

Set Operators Precedence

There are some compound queries that are using more than one different set operator. The order in which the queries will be placed in the compound statement is determined through the set operator precedence. The Intersect set operator have the highest precedence over all other set operators. However, if the user uses parentheses around a query then that query will be called first.

But the MySQL does not allow the intersect operator and parentheses in the compound queries. Therefore, the user should carefully arrange the queries in an order to get the desired results.

---