Join, Projection and Selection

The relational operators join, projection and selection will be described below using a simple example.

Join Conditions

The relational operator "join" can be used to collect several tables in a cross-product table. Individual fields of the tables are identified with one another using join conditions.

We will look at the contents of the two tables TABA and TABB.

TABA

  

TABB

Field1

Field2

  

Field3

Field4

Field5

1

Text1

  

1

A

Text3

2

Text2

  

1

B

Text4

     

2

A

Text5

     

2

B

Text6

The cross-product table now contains all the combinations of records of TABA with records of TABB.

Field1

Field2

Field3

Field4

Field5

1

Text1

1

A

Text3

1

Text1

1

B

Text4

1

Text1

2

A

Text5

1

Text1

2

B

Text6

2

Text2

1

A

Text3

2

Text2

1

B

Text4

2

Text2

2

A

Text5

2

Text2

2

B

Text6

Since the whole cross-product is not usually a sensible selection, it must be restricted using a join condition. In our example, Field1 of table TABA must be identified with Field3 of table TABB. The join condition is therefore:

TABA-FIELD1 = TABB-FIELD3.

The cross-product table reduced by this join condition is then:

Field1

Field2

Field4

Field5

1

Text1

A

Text3

1

Text1

B

Text4

2

Text2

A

Text5

2

Text2

B

Text6

The join condition can be specified explicitly during view maintenance. It is also possible to use an existing foreign key. In this case, the join condition is automatically generated from the definitions in the foreign key ( Foreign Key Relationship and Join Condition).

Projection

The relational operator projection can be used to remove fields from a cross-product table which was created by using joins. Information which is not required can be masked out in this way. Projection can thus be implemented by explicitly selecting the fields to be used in the view.

In the above example, only fields 1, 2 and 5 are copied. The view then has the following form:

Field1

Field2

Field5

1

Text1

Text3

1

Text1

Text4

2

Text2

Text5

2

Text2

Text6

Selection

Selection conditions, which are to be used as a filter, can be defined for a view.

Restrictions can be defined for the fields of the view in these selection conditions. In this case, only those data records which satisfy the restrictions are used in the view. In such selection conditions, a field of the view is compared with a constant using a comparison operator. Several such selection conditions can be linked with the logical operators AND and OR.

In the above example, only data records in which Field 2 has the contents ‘Text1’ should be used in a view. The corresponding selection condition is

TABA-Field2 = ‘Text1’.

The view then has the following form:

Field1

Field2

Field5

1

Text1

Text3

1

Text1

Text4

See also:

Inner and Outer Join