Union, minus and intersect commands in sql

Hi Naresh,

Please find below link which will be help ful to understand,

https://www.c-sharpcorner.com/article/set-operators-sql-union/

https://learnsql.com/blog/introducing-sql-set-operators-union-union-minus-intersect/

https://www.c-sharpcorner.com/article/sql-operators/

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:

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

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 Union operation gives us distinct values. If we want to allow 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];

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 syntax for the minus operation is as follows:

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

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 syntax for the intersection operation is as follows:

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