## TOPICS (Click to Navigate)

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

## 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