Difference between Equi Join and Non-Equi Join in SQL with Examples

Hello guys, If you want to learn about the difference between Equi and Non-Equi join in SQL then you have come to the right place. Earlier, I have shared the difference between LEFT and RIGHT join in SQL and now we will understand equi and non-equi join in SQL and database. The term "Non-Equi join" in SQL Server may sound abstract, but it's far from it. Equi joins are the same way. I think you'll agree with me after reading this essay. I'll attempt to explain what they are and when they should be used today. So, let's get this party started. 

SQL's capacity to aggregate and alter data from several tables is one of its most powerful characteristics. You'd have to store all of the data pieces required for each application in a single table if you didn't have this functionality. You'd have to store the same data in several tables if you didn't use common tables. 

Consider having to rethink, rebuild, and repopulate your tables and databases every time a user wanted a query with new data. Using SQL's JOIN command, you may create smaller, more focused tables that are easier to maintain than bigger tables.

What are Equi join and Non-Equi join?

Equi-joins are conventional joins in which the equality operator (=) is used to join tables together. Equi-connections are merely a fancy way of referring to such "ordinary" joins. This applies to joins that employ a foreign key, as well as joins that compare characteristics that aren't part of a foreign key (this is rarely used). 

So, to understand Equi joins and Non-Equi joins better, let's create some data and work on it to show live examples. Let's create 2 tables named cricketer and franchise. Hope you all are IPL fans :p

CREATE TABLE cricketer(id int(50) primary key NOT NULL,
                      name varchar(256),
                      salary int(50));

                      
CREATE TABLE franchise(id int primary key,
                      cricketer_id int(50) NOT NULL,
                      name varchar(256)
                      ); 

Now, as the tables are created, let's insert some data into them using a few insert queries. You guys can go ahead and enter as many records as you please, I would, on the other hand, will enter a few records as shown below : 

INSERT INTO cricketer values(1,'Virat',100);   
INSERT INTO cricketer values(2,'Rohit',99);
INSERT INTO cricketer values(3,'Bumrah',98);
INSERT INTO cricketer values(4,'Chahal',2);

INSERT INTO franchise values(1,1,'rcb');
INSERT INTO franchise values(2,4,'gt');
INSERT INTO franchise values(3,2,'mi');
INSERT INTO franchise values(4,3,'dc');

The tables would look like the below : 

Cricketer table : 

Franchise table : 

Now, for checking what exactly Equi join would produce, as a result, let's perform inner join, a perfect example of Equi joins.

select * from cricketer inner join franchise on cricketer.id = franchise.cricketer_id;

The result would be as below : 

Non-Equi join

Some intriguing query difficulties can be solved using a Non-Equi join. A Non-Equi join can be used to check for duplicate values or to examine if one value in one table falls inside a range of values in another.

Now, let's use some Non-Equi join examples on our data and get some interesting results.

select * from cricketer inner join franchise on cricketer.id <> franchise.cricketer_id;

This query would yield results that would have cricketer.id not equal to the franchise.cricketer_id. You guys could have also used the != operator.

The results are as shown below : 

Now, let's look at another query of Non-Equi join. Taking the same data of our two tables, cricketer and franchise, let's suppose we have a requirement of gathering data where cricketer.id is greater than a franchise.cricketer_id. Yes! I know business logic can make us do anything :p Let's write a query for this.

select * from cricketer inner join franchise on cricketer.id > franchise.cricketer_id;

The result from the above query will yield results as below : 

When you connect tables in SQL Server, whether it's an Equi join or a Non-Equi join, you'll almost always need a foreign key. After all, the goal of databases is to join tables in this way. In most circumstances, relating tables using values from conceptually unrelated characteristics is not reasonable (right, smart, or whatever adjective you want to use). Still, if you want (need) to do it for whatever reason, you can.

Applications of Non-Equi joins

There are a few reasons why we should utilize Non-Equi joins. Among the most popular applications are:

• matching against a range of values 
• calculating running totals 
• checking for duplicate data between tables

Non-Equi joins and inner joins are both considered SQL inner joins if that helps!

Conclusion

That's all about difference between equi join and non-equi join in SQL. I hope that today's post provided a concise but clear explanation of what SQL Server Non-Equi joins are and when they should be used. They're seldom utilized, so use them wisely if you do decide to use them. Also, keep an eye out for future stories.