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;

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

Go to Natural Join in SQL page

Go to Multiple choice questions in SQL/Relational algebra page