Equi-join in relational algebra

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

***********

Go to Relational Algebra Operations page