One of the characteristics of RDBMS is that it should support all the transaction on the records in the table by means relational operations. That means it should have strong query language which supports relational algebra. There are three main relational algebras on sets – UNION, SET DIFFERENCE and SET INTERSECT. The same is implemented in database query language using 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.
Table of Contents
There are 3 main set operators used in the query language.
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;
We can notice that Result will have same column names as first query. Duplicate record – 104 from EMP_TEST and EMP_DESIGN are showed only once in the result set. Records are sorted in the result.
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 duplicate records 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;
We have INTERSECT ALL operator too. But it is same as INTERSET. There is no difference between them like we have between UNION and UNION ALL.
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;
We can notice in the above result that only the records that do not exists in EMP_DESIGN are displayed in the result. The record which appears in both the tables is eliminated. Similarly, the records that appear in second query but not in the first query are also eliminated.