Equi Join and Non-Equi Join in SQL

Introduction

Whenever we have to retrieve data from a table, we use selection, projection, and join methods. Selection is a method in which we have to retrieve data using a condition such as a WHERE condition from a single table. Projection is a method in which we have to retrieve data from a single table without using a condition. We use joins if we want to select or retrieve data from multiple tables. It retrieves data that includes related rows/data from multiple tables.

Equi and Non-Equi Join in SQL are types of joins that retrieve data from multiple tables based on some condition. Equi Join in SQL will retrieve only the data that is equal in both the tables or only retrieves the matching column values; therefore, it uses an equality operator to join multiple tables. On the other hand, Non-Equi join is used to retrieve data without using the equality operator. However, we can use other operators except the equality one in the non-equi join to join multiple tables.

EQUI JOIN in SQL

Equi Join in SQL is a type of INNER Join that displays the output by performing a join operation between two or more tables based on the common column between them. It uses the equality ( = ) symbol to compare the data between two columns; if the data matches, it retrieves it. Equi Join compares each column value of the source table with each value in the corresponding target table, and if both the values are equal, it retrieves it.

The Equi Join in SQL returns only the data in all the tables we are comparing based on the common column field. It does not display null or unmatchable data. The equality operator in the Equi Join operation is used to refer to the equality in the WHERE clause. However, it returns the same result when we use the JOIN keyword with the ON clause along with column names and their respective tables.

Syntax:

OR

Example:

Suppose we have two tables, namely state and city, which contain the name of the states and the name of the cities, respectively. In this example, we will map the cities with the states in which they are present.

The table state is shown below:

The table city is shown below:

Now, if we execute a query of Equi-join using the equality operation and the WHERE clause, then-

Output:

We can try the above example with the second syntax-

Therefore, we can retrieve the data from two tables using these two methods.

NON-EQUI JOIN in SQL

Non-Equi Join is also a type of INNER Join in which we need to retrieve data from multiple tables. Non-Equi Join matches the column values from different tables based on an inequality based on the operators like <,>,<=,>=,!=, BETWEEN, etc.

Non-Equi Join in SQL retrieves data using any operator or condition except the equality condition. The value of the column in each row from the source table is compared with the corresponding value of the target table. If the data matches in the source and target table, the comparison returns true, and therefore that data is retrieved from the table.

However, we use the Non-Equi joins for the below-mentioned reasons-

1. Retrieving data matching in a range of values.
2. Checking for duplicate data between tables.
3. For calculating totals.

Syntax:

Example:

1. We take two tables, test1 and test2.

   Table test1 as shown below -

   S_NO	Name
   20	Amit
   30	Ankush
   10	Akash
   50	Jatin

   Table test2 is shown below -

   S_NO	Name
   80	Tarun
   60	Mitali
   10	Akash
   50	Jatin
   5	Aman

   Now, if we execute a query of Non-Equi-join using any operator other than the equality operator, such as >(greater than) with the WHERE clause -

Output:

In the above example, we are combining the tables based on their SNo. All those entries will be retrieved whose SNo of the test1 table is greater than the SNo of the test2 table.