What are UNION, MINUS, and INTERSECT commands in DBMS?

In DBMS, we deal with multiple entities and perform various operations on them. Some of the most commonly used database operations are union, minus, and intersect. So in this blog, we'll learn about these DBMS commands in detail with their implementations.

Note: In this blog, We will take all the examples using the MySQL database only. We'll be using the below-mentioned colors_a and colors_b table for demonstrating the examples. The tables are as follows:

Now let us learn about these database operations one-by-one.

UNION

The Union is a binary set operator in DBMS. It is used to combine the result set of two select queries. Thus, It combines two result sets into one. In other words, the result set obtained after union operation is the collection of the result set of both the tables.

But two necessary conditions need to be fulfilled when we use the union command. These are:

Both SELECT statements should have an equal number of fields in the same order.
The data types of these fields should either be the same or compatible with each other.

The Union operation can be demonstrated as follows:

The syntax for the union operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] UNION SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the union operation can be as follows:

SELECT color_name FROM colors_a UNION SELECT color_name FROM colors_b;

The returned values for the above query is as follows:

The Union operation gives us distinct values. If we want to allow the duplicates in our result set, we'll have to use the 'Union-All' operation.

Union All operation is also similar to the union operation. The only difference is that it allows duplicate values in the result set.

The syntax for the union all operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] UNION ALL SELECT (coloumn_names) from table2 [WHERE condition];

The MySQL query for the union all operation can be as follows:

SELECT color_name FROM colors_a UNION ALL SELECT color_name FROM colors_b;

The returned values for the above query is as follows:

MINUS

Minus is a binary set operator in DBMS. The minus operation between two selections returns the rows that are present in the first selection but not in the second selection. The Minus operator returns only the distinct rows from the first table.

It is a must to follow the above conditions that we've seen in the union, i.e., the number of fields in both the SELECT statements should be the same, with the same data type, and in the same order for the minus operation.

The minus operation can be demonstrated as follows:

The syntax for the minus operation is as follows:

SELECT (coloumn_names) from table1 [WHERE condition] MINUS SELECT (coloumn_names) from table2 [WHERE condition];

Note: It is to be noted that the minus operator is not present in MySQL. But we can make use of either 'NOT IN' operator or ' JOIN ' for performing a minus operation in MySQL.

Here, we first see the 'NOT IN' operator for demonstrating the examples.

The MySQL query for the minus operation using the 'NOT IN' operator can be as follows:

SELECT color_name FROM colors_a WHERE color_name NOT IN(SELECT color_name FROM colors_b);

The returned values for the above query is as follows:

The MySQL query for the minus operation using ' JOIN ' can be as follows:

SELECT color_name FROM colors_a LEFT JOIN colors_b USING (color_name) WHERE colors_b.color_name IS NULL;

The returned values for the above query is as follows:

INTERSECT

Intersect is a binary set operator in DBMS. The intersection operation between two selections returns only the common data sets or rows between them. It should be noted that the intersection operation always returns the distinct rows. The duplicate rows will not be returned by the intersect operator.

Here also, the above conditions of the union and minus are followed, i.e., the number of fields in both the SELECT statements should be the same, with the same data type, and in the same order for the intersection.

The intersection operation can be demonstrated as follows:

The syntax for the intersection operation is as follows:

SELECT (coloumn_names) from table1[WHERE condition] INTERSECT SELECT (coloumn_names) from table2 [WHERE condition];

Note: It is to be noted that the intersect operator is not present in MySQL. But we can make use of either 'IN' or 'Exists' operator for performing an intersection operation in MySQL.

Here, we are using the 'IN' clause for demonstrating the examples.

The MySQL query for the intersection operation using the 'IN' operator can be as follows:

SELECT color_name FROM colors_a WHERE color_name IN(SELECT color_name FROM colors_b);

The returned values for the above query is as follows:

This is all about the UNION, MINUS, INTERSECT operators in DBMS. Hope you learned something new today. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!

AfterAcademy