Sunday, October 9, 2016

Muhammad Akbar

Relational Set Operators in DBMS-Tutorial

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.

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.

                                 

  System Catalog

Muhammad Akbar

About Muhammad Akbar -

Author Description here.. Nulla sagittis convallis. Curabitur consequat. Quisque metus enim, venenatis fermentum, mollis in, porta et, nibh. Duis vulputate elit in elit. Mauris dictum libero id justo.

Subscribe to this Blog via Email :

Hello Every One thanks For Visit My site You Can Suggest any Idea About site And Related Education Information Provide me Its Best Content For you am Include in this Site And feel free contact And email now I hope This site very help full you