Relational set operators:

Relational set operators are used to combine or subtract the records from two tables. These operators are used in the SELECT query to combine the records or remove the records.

In order to set operators to work in database, it should have same number of columns participating in the query and the datatypes of respective columns should be same. This is called Union Compatibility.

The resulting records will also have same number of columns and same datatypes for the respective column.

There are 4 main set operators used in the query language.

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION

It combines the similar columns from two tables into one resultant table. All columns that are participating in the UNION operation should be Union Compatible. This operator combines the records from both the tables into one. If there are duplicate values as a result, then it eliminates the duplicate. The resulting records will be from both table and distinct.

Suppose we have to see the employees in EMP_TEST and EMP_DESIGN tables. Suppose we don’t have UNION operator. What we will be doing is, select the records from EMP_TEST.

Copy it into some file. Then select the records from EMP_DESIGN and copy it to the same file as previous. Thus we will get the result in one file. If we are using UNION, then it will combine both the results from tables in to one set.

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST

UNION

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;

UNION ALL

This operation is also similar to UNION, but it does not eliminate the duplicate records. It shows all the records from both the tables. All other features are same as UNION. We can have conditions in the SELECT query. It need not be a simple SELECT query.

Look at the same example below with UNION ALL operation.

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST

UNION ALL

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;

INTERSECT

This operator is used to pick the records from both the tables which are common to them. In other words it picks only the duplicaterecords from the tables. Even though it selects duplicate records from the table, each duplicate record will be displayed only once in the result set. It should have UNION Compatible columns to run the query with this operator.

Same example above when used with INTERSECT operator, gives below result.

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST

INTERSECT

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;

MINUS

This operator is used to display the records that are present only in the first table or query, and doesn’t present in second table / query. It basically subtracts the first query results from the second.

Let us see the same example with MINUS operator.

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST

MINUS

SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;