theta join in relational algebra

Theta join in relational algebra, theta join in relational model, theta join relational algebra query and its equivalent SQL queries, binary theta join operation in relational algebra

Theta Join in Relational algebra

Operation	THETA JOIN
Type of operation	Binary
Syntax	R1 ⋈ <join condition> R2 Here, the <join condition> is of the form R1.a θ R2.b, and θ is any of the comparison operators {=, <, <=, >, >=, ≠}
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) 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. 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
Example 3	AGE_GROUP Min_Age Max_Age Age_Group 18 30 18-30 31 60 31-60 61 100 Above 60 CUSTOMER Customer_Name Age Rahul    35 Meera 25 Steve 62 RA: πcustomer_name, age_group(σage >= min_age ^ age <= max_age (customer X age_group)) SQL: SELECT customer_name, age_group FROM customer, age_group WHERE age between min_age and max_age; Result: Joins two tables and accepts the records that satisfy the condition given in the WHERE clause. πcustomer_name, age_group(σage >= min_age ^ age <= max_age (customer X age_group)) Customer_Name Age_Group Rahul    31-60 Meera 18-30 Steve Above 60 Note: THETA join uses any comparison operator in joining two tables.

***************

Go to Relational Algebra Operations page