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
|
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Example 3
|
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.
Note:
THETA join uses any comparison operator in joining two tables.
|
***************
Go to Relational Algebra Operations page
No comments:
Post a Comment