Advanced Database Management System - Tutorials and Notes: natural join in relational algebra and sql

Search Engine

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

Sunday, 27 August 2017

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.



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




No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents