Equi Join and Non-Equi Join in SQL
An equi join is any JOIN operation that uses only and only the equals sign. If there is a query with more than one join condition, out of which one condition has an equals sign, and the other doesn't, then this query would be considered a non-equi join in SQL. Thus Equi Joins in SQL joins multiple tables on the basis of an equality condition. In contrast, the Non-Equi Join joins the table on the basis of conditions other than the equality conditions, such as !=, >, <, etc.
This article covers
- A brief introduction of joins, why we need them, and their usage.
- We will also have a detailed explanation about Equi and Non-Equi Joins.
- The syntax and examples are discussed to give a clearer understanding of the topic.
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 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 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 operation 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.
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-
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-
- Retrieving data matching in a range of values.
- Checking for duplicate data between tables.
- For calculating totals.
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 -
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.
- Joins are used to retrieve data from multiple tables based on a common column.
- Equi Join and Non-Equi Joins are types of Inner Joins.
- Equi Join in SQL is used to retrieve data from multiple tables using an equality condition with the WHERE clause.
- Non-Equi in SQL is used to retrieve data from multiple tables using any other operator except the equality condition.
- We can use Non-Equi joins in SQL to check for duplicate data, retrieve data between a range of values, and calculate totals of data or values.
- In both the Equi and Non-Equi joins, each value of the common column of the source table is compared with the value in the target table and based on the equality or inequality conditions present in the query, data will be retrieved from multiple tables.