Equi Join and Non-Equi Join in SQL (with Examples)

Equi Joins and Non-Equi Joins are used to retrieve data from different tables of a schema based on given conditions, but there are differences between them. In this article, we will explore more about Equi and Non-Equi Joins which are a superset of these joins in SQL.

What is Equi Join in SQL?

We have already learned about different types of joins which helps us to easily find commonality between different tables.

Equi Joins in SQL are used to perform operations between different tables where there are common columns.

They are more or less like INNER Joins, they compare different column data based on similarities given. It usually uses the equality “=” sign to compare the data between given columns. It matches the data until and unless the requirements are met and retrieves it.

Equi Joins only retrieves the data from the tables once the compared columns are matched against given conditions. It does not fetch unmatchable data. Equi Join uses the WHERE condition with the equality  “= “ symbol.

Syntax:

SELECT
column1,
column2,
column3
FROM
table1
JOIN
table2 ON table1.column = table2.column;

Let us understand the use of Equi-Join through an example

Table: Employees

Table: Departments

SQL Query:

SELECT
E.EmployeeID,
E.FirstName,
E.LastName,
D.DepartmentName,
E.Salary
FROM
Employees E
JOIN
Departments D ON E.DepartmentID = D.DepartmentID;

Output:

In this example, we used Equi-Join between the Employees and Departments tables based on the common column to retrieve insightful information.

What is Non-Equi Join in SQL?

Non-Equi Joins are used in SQL to combine the data of two or more tables based on a condition that is not specifically equal or similar; rather they are unequal operations. It uses conditions such as less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), or not equal to (<>) to join the data of the tables.

Syntax:

SELECT
column1,
column2,
column3
FROM
table1
JOIN
table2 ON table1.column = table2.column
WHERE
non-equi condition;

Table: Customers

Table: Orders

SQL Query:

SELECT
O.OrderID,
C.CustomerName,
C.CreditLimit,
O.OrderTotal
FROM
Customers C
JOIN
Orders O ON C.CustomerID = O.CustomerID
WHERE
O.OrderTotal > C.CreditLimit;

Output:

In the above example, we have used non-equi join between the Customers and Orders tables. We have used the condition for the join where the total amount of order is greater than the credit limit (OrderTotal > CreditLimit). The output includes columns from both tables where the order total is greater than the customer's credit limit.

Difference between Equi and Non-Equi Joins

Conclusion

In a nutshell, through this article, we were able to distinguish between Equi Joins and Non-Equi Joins. Through appropriate examples, we were able to point out key differences and demonstrate them in our table. Furthermore, the article dealt with the practical application of joins, providing an example of how these joins can be effectively utilized in different tables to find common grounds.