Set operation: – The basic definition of table is relation and used with set of element. Thus in relational data base all the data is stored in set. The quarry also returns set of rows, so we can do addition set operation on the output of quarry. The set operations are union, intersect and minus.

Union: – The union operation is used in data quarry language to collect all data from selected tables. Suppose there are two sets S and R. With the following element.

S={A,B,C} and R={X,Y,Z}

The union of sets S and R is given by the elements.

S union R={A,B,C,X,Y,Z}

The condition is doing operation on two set is that the set element of one set should be compatible with the set element of the other set. If set element of one set consist of element of one part, then other set element should also have one part with compatible data type. An example of union operator with select statement is as shown below –

                Select location from company where CompanyName=“ACC” union select location from employee where CompanyName=“Tata”;

In case of above example, two tables are used with select statement. Name of first table is company and second is employee. We select only location column. From tables company and employee. The above statement will display listing of location column from both tables which is available in the table.

Intersect: – The intersect is used for finding out common elements of all sets. Suppose we insertion of two sets S and R as follows.

S={A,B,C} and R={X,Y,Z}

The intersect operation can be used with following format S intersect R={A}. An example of intersect operator is –

                Select location from company where CompanyName=“ACC” intersect select location from employee where CompanyName=“Tata”;

In case of above example, only those column values are listed from tables employee and company which are commonly available in both tables.

Minus: – The minus operator is used for finding out elements which are available in first set but not available in second set. Suppose we have two sets S and R.

S={A,B,C} and R={A,X,Y}

Then S minus R={B,C}

An example of select statement with minus operator.

                Select location from CompanyName=“ACC” minus select location from employee where CompanyName=“Tata”;

In case of above example location column value listed for table company only when it is not available in table employee.

Advertisements