Extended Operators in DBMS

Extended Operators in DBMS

 

In this article, we will learn about Extended Operators in DBMS. There are different types of Extended Operators which is explained below in brief.

Extended Operators in DBMS

Relational Algebra in DBMS

  • Relational algebra is a widely used procedural query language.
  • Relational algebra collects instances of relations as input and gives occurrences of relations as output by using various operations.
  • Relational algebra operations are performed recursively on a relation.
  • The output of relational algebra operations is a new relation, which can be formed from one or more input relations.

 

Extended Operators in Relational Algebra:

Extended operators in Relational algebra are:

  •  Natural Join (⋈)
  • Left, Right, Full outer join (⟕, ⟖, ⟗)
  • Intersection (∩)

 

Learn more about Relational Algebra here on this page.

 

 

Natural Join (⋈)

  • JOIN operation also allows joining variously related tuples from different relations.
  • Natural join can only be performed if there is a common attribute (column) between the relations.
  • The name and type of the attribute must be same.

Example:

Consider the following two tables:

Table C

NumSquare
24
39

 

Table D

NumCube
28
327

 

C ⋈ D
NumSquareCube
248
3927

 

 

Outer Join

  • In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.
  • There are three types of outer joins. They are
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join
  • Consider the following 2 Tables :

Table A

NumSquare
24
39
416

 

Table B

NumCube
28
327
5125

 

Left Outer Join(A  B)

  • In the left outer join, the operation allows keeping all tuple in the left relation.
  • If there is no matching tuple is found in the right relation, then the attributes of the right relation in the join results are filled with null values.

Example:

A  B
NumSquareCube
248
3927
416

 

Right Outer Join(A B)

  • In the right outer join, the operation allows keeping all tuple in the right relation.
  • If there is no matching tuple is found in the left relation, then the attributes of the left relation in the join results are filled with null values.

Example:

AB
NumSquareCube
244
399
5125

 

Full Outer Join: ( A  B)

In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.

A  B
NumCubeSquare
248
3927
416
5125

 

 

Intersection (∩)

  • An intersection is defined by the symbol ∩
  • A ∩ B defines a relation consisting of a set of all tuple that is in both A and B. However, A and B must be union-compatible.

Example:

Consider 2 tables –

Table A :

Attribute1Attribute2
11
12

Table B :

Attribute1Attribute2
11
13

 

A ∩ B
Attribute1Attribute2
11