Thursday, March 20, 2014

Equi-Join



Equi-Join

Equi-Join is a joining technique which uses equality operator (=) to match columns of two different tables to join those tables.

Consider the following query which performs equijoin;
SELECT * FROM Student, Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;

Example

Let us take the following tables, Student (which stores personal information about students), Course_registration (which stores information about courses registered by students) to explain equi-join.
RegNo
SName
Gen
Phone
R1
Sundar
M
9898786756
R3
Karthik
M
8798987867
R4
John
M
7898886756
R2
Ram
M
9897786776
Table 1 – STUDENT

RegNo
Courses
R1
Database
R2
Database
R3
Data Structures
R4
Multimedia
Table 2 – COURSE_REGISTRATION

After the execution of query, we will get the following table as the result, where every record of Student is joined with only matching records in Course_Registration.
RegNo
SName
Gen
Phone
RegNo
Courses
R1
Sundar
M
9898786756
R1
Database
R2
Ram
M
9897786776
R2
Database
R3
Karthik
M
8798987867
R3
Data Structures
R4
John
M
7898886756
R4
Multimedia
Table 3 – Joined Table

How the tables are compared internally?

In the join query, you carefully observe the condition given in the WHERE clause.
SELECT * FROM Student, Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;

The WHERE clause includes a condition which matches the RegNo column of STUDENT with RegNo column of COURSE_REGISTRATION. You notice the order of records in Table 1 and Table 2. The records are actually of different order of RegNo values. So, how these two tables are joined in Table 3 correctly?
Joining is done actually using the Join condition attributes RegNo of both tables. Here, to match the student records with the courses registered records, the system matches every RegNo value of STUDENT table with every RegNo value of COURSE_REGISTRATION table. If both values are same, the concerned records are included in the result set. If not, the records are discarded. In other words,
4 (records) X 4 (records) = 16 records
are compared to arrive at the result as given below. Here, the matched records are included in the final result, other records are not. Hence, out of 16 comparisons only 4 are matched correctly, 12 are discarded.
Student.RegNo = Course_Registration.RegNo
Included or not?
1st record of STUDENT R1 = R1
Yes
R1 = R2
No
R1 = R3
No
R1 = R4
No
2nd record of STUDENT R3 = R1
No
R3 = R2
No
R3 = R3
Yes
R3 = R4
No
3rd record of STUDENT R4 = R1
No
R4 = R2
No
R4 = R3
No
R4 = R4
Yes
4th record of STUDENT R2 = R1
No
R2 = R2
Yes
R2 = R3
No
R2 = R4
No

Notes:

1. We are matching all the values of joining attribute of one table with all the values of joining attribute of other table, because in RDBMS we do not impose arranging records in any particular order physically.
2. The joining attributes need not be with same name. They can have different names. Only condition is they both must be of same domain.
3. In the result both columns (joining attributes) are included. See Table 3. In Natural Join, only one will be shown in the final result. The other one will be treated as redundant.
4. The above given equi-join  query can also be written as follows in SQL;
SELECT * FROM Student JOIN Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;



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

data recovery