natural join in relational algebra and sql

Natural join in Relational algebra and SQL, natural join as in relational model, natural join examples with equivalent sql queries, difference between natural join and equijion

Natural join in Relational Algebra

Operation	NATURAL JOIN
Type of operation	Binary
Syntax	R1 ⋈ R2
Equivalent expression	R1 ⋈ R2 = σ<join condition>(R1 X R2) Here, the <join condition> is the Equijoin condition that checks for same/similar attributes of R1 and R2.
Function	Same as EQUIJOIN except that the join attributes of R2 are not included in the resulting relation; if the join attributes have the same names, they do not have to be specified at all. If R1(A, B, C) is joined with R2(A, D, E), then it produces a new temporary relation R(A, B, C, D, E). The result consists of all records that satisfy the join condition. Also, observe that R does not include A twice. Natural join is just like equi-join. Only difference is that the common attribute is not included in the result twice in natural join unlike equi-join.
Example 1	STUDENT Regno Name Age Phone 2 records BIT001 Ram 15 8652398452 BIT023 Selvi 17 7894562310 SUB_REGD Regno Subject 3 records BIT001 Physics BIT023 Computer BIT023 OS STUDENT X SUB_REGD Regno Name Age Phone Regno 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: (student ⋈ sub_regd) SQL: SELECT regno, name, age, phone, subject FROM student, sub_regd WHERE student.regno = sub_regd.regno; Result: Joins the two relations student and sub_regd on regno attributes. If the values of the join attributes are same, only those records are combined as shown below. student ⋈ sub_regd Regno Name Age Phone Subject BIT001 Ram 15 8652398452 Physics BIT023 Selvi 17 7894562310 Computer BIT023 Selvi 17 7894562310 OS
Example 2	RA: (Π regno, name, age, phone (student)) ⋈ (Π regno, subject (sub_regd) SQL: SELECT * FROM student natural join sub_regd; 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, name, age, phone (student)) ⋈ (Π regno, subject (sub_regd) Regno Name Age Phone Subject BIT001 Ram 15 8652398452 Physics BIT023 Selvi 17 7894562310 Computer BIT023 Selvi 17 7894562310 OS Example 1 and 2, both produces same result. Note: If you have common attributes with different names, then you can use rename operation before perform natural join.

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

Go to Relational Algebra Operations page