Advanced Database Management System - Tutorials and Notes: Equi-join in relational algebra

## Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## Equi-join in relational algebra, equi-join in relational model, equi-join relational algebra query and its equivalent SQL queries, equi-join examples

### Equi-Join in Relational Algebra

Operation
EQUI-JOIN
Type of operation
Binary
Syntax
R1 <join condition> R2
Here, the <join condition> is of the form R1.a = R2.b.
Equivalent expression
R1 <join condition> R2 = σ<join condition>(R1 X R2)
Function
Produces all combinations of tuples from R1 and R2 that satisfy the join condition. This join condition involves attributes from both relations such as follows;
R1.a = R2.b
If R1(A1, A2, …, An) is joined with R2(B1, B2, …, Bn) using the condition R1.A1 = R2.B1, then it produces a new temporary relation R(A1, A2, …, An, B1, B2, …, Bn) and every tuple (record) of R1 is combined with every tuple of R2 where R2.B1 value is same as the R1.A1 value. The result consists of all records that satisfy the join condition.
Example 1

 STUDENT Regno Name Age Phone 2 records BIT001 Ram 15 8652398452 BIT023 Selvi 17 7894562310

 SUB_REGD SRegno Subject 3 records BIT001 Physics BIT023 Computer BIT023 OS

 STUDENT X SUB_REGD Regno Name Age Phone SRegno Subject 2 X 3 = 6 records BIT001 Ram 15 8652398452 BIT001 Physics BIT001 Ram 15 8652398452 BIT023 Computer BIT001 Ram 15 8652398452 BIT023 OS BIT023 Selvi 17 7894562310 BIT001 Physics BIT023 Selvi 17 7894562310 BIT023 Computer BIT023 Selvi 17 7894562310 BIT023 OS

RA: σregno = sregno (student X sub_regd)
SQL: SELECT * FROM student, sub_regd WHERE regno = sregno;
Result: Joins the two relations student and sub_regd on regno and sregno attributes. If the values of the join attributes are same, only those records are combined as shown below.
 σregno = sregno (student X sub_regd) Regno Name Age Phone SRegno Subject BIT001 Ram 15 8652398452 BIT001 Physics BIT023 Selvi 17 7894562310 BIT023 Computer BIT023 Selvi 17 7894562310 BIT023 OS
Example 2
RA: σregno = sregno(Π regno, name, phone (student) X sub_regd)
SQL: SELECT regno, name, phone FROM student, sub_regd WHERE regno = sregno;
Result: Produces a new temporary relation with regno, name, phone, sregno and subject attributes of all students. The records that satisfy the join condition regno = sregno are included in the final result.
 σregno = sregno (Π regno, name, phone (student) X sub_regd) Regno Name Phone SRegnoubject Subject BIT001 Ram 8652398452 BIT001 Physics BIT023 Selvi 7894562310 BIT023 Computer BIT023 Selvi 7894562310 BIT023 OS

***********