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

## 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

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