Equi Join

Equi Join An equijoin is is a join condition having an equality operator on tables having rows containing similar values. Here care must be taken to include all the required columns in the join condition. 

The join could be tables having a 1to 1 mapping or a 1 to many relation.

 If a join is made on 2 tables having a 1 to 1 mapping of rows between the two tables, an ideal table design would be to have a primary key defined on the join columns on both the tables and to include all the required columns of the primary key in the join condition.

If the join is a 1 to many join, the join column could be a foreign key in the secondary table and index could be created on this column in the secondary table.

Below is an example of an employee and his office location details. Here at a time the employee could be only in 1 office location. We have a join of 4 tables here based on the principles of normalisation.

The employee details are in EMPLOYEES table.

The mapping of the employee to a location is in the EMP_LOCATION_DETAILS table.

The location address details are in the LOCATIONS table.

The country details for the employee location are in the COUNTRIES table.
 

SELECT emp.first_name,
       emp.last_name,
       loc.street_address,
       loc.postal_code,
       loc.city,
       loc.state_province,
       cnt.COUNTRY_NAME
FROM EMPLOYEES emp,
     EMP_LOCATION_DETAILS e1d,
     LOCATIONS loc,
     COUNTRIES cnt
WHERE emp.EMPLOYEE_ID =  e1d.EMPLOYEE_ID
  AND e1d.LOCATION_ID = loc.LOCATION_ID
  AND loc.COUNTRY_ID = cnt.COUNTRY_ID
  AND emp.EMPLOYEE_ID IN (100,101,102,103,104);