Equi Join in SQL

Examples

Let's consider two tables, "employees" and "departments", with the following structures:

CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  department_id NUMBER
);

CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(50)
);

Output

To retrieve the employee names along with their corresponding department names, you can use an EQUI JOIN like this:

SELECT 
  employees.first_name || ' ' || employees.last_name AS name, 
  departments.department_name
FROM 
  employees
JOIN 
  departments 
ON 
  employees.department_id = departments.department_id;

Output

This query will return a result set with two columns: "name" from the "employees" table and "department_name" from the "departments" table. The JOIN condition `employees.department_id = departments.department_id` ensures that only the rows with matching department IDs are combined.

Equi Join Using Three Tables

EQUI JOIN can also be used to join more than two tables. Let's add a third table, "salaries", with the following structure:

CREATE TABLE salaries (
  employee_id NUMBER PRIMARY KEY,
  salary NUMBER
);

Output

To retrieve the employee names, their department names, and their salaries, you can use an EQUI JOIN across all three tables:

SELECT 
    employees.employee_id, 
    employees.first_name, 
    employees.last_name, 
    departments.department_name, 
    salaries.salary
FROM 
    employees
JOIN 
    departments ON employees.department_id = departments.department_id
JOIN 
    salaries ON employees.employee_id = salaries.employee_id;

Output

In this example, the "employees" table is joined with the "departments" table based on the "department_id" column, and then the result is further joined with the "salaries" table based on the "employee_id" column. The final result set will contain the employee names, department names, and their corresponding salaries.

Difference between Natural Join, Equi Join and Inner Join:

Frequently Asked Questions

Can Equi Join be used with more than two tables?

Yes, Equi Join can be used to join multiple tables by specifying join conditions between each pair of tables.

Is Equi Join case-sensitive?

Equi Join is case-sensitive when comparing string values. If the column collation is case-insensitive, the join will be case-insensitive.

What happens if there are no matching rows in an Equi Join?

If there are no matching rows based on the join condition, those rows will be excluded from the result set.

Conclusion

In this article, we discussed Equi Join, a type of SQL JOIN that combines rows from multiple tables based on matching values in specified columns. We talked about the syntax of Equi Join & saw examples of joining two or more tables. We also discussed the differences between Natural Join, Equi Join & Inner Join. Equi Join provides a powerful way to retrieve related data from multiple tables, allowing for efficient querying & data analysis.

You can also practice coding questions commonly asked in interviews on Coding Ninjas Code360. 

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, System Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.