Non-Equi join in SQL Server sounds like something abstract (and fancy), but it’s not so abstract (and fancy) at all. The same stands for equi joins. After reading this article, I hope you’ll agree on that with me. Today’ I’ll try to explain what they are and when you should use them. So, let’s start.
On this part, nothing has changed since the last article, so we’ll use the same model we’re using throughout this series.
If you’re still not familiar with it, take some time to see how the tables are related to each other. We’ll use only two tables from this model city and country, and we’ll comment on the data later in this article while taking a look at few non-equi join SQL Server queries. If you need to refresh your knowledge related to primary and foreign keys, select statement, and inner and left joins, this would be the right time to do it.
Equi-Joins vs. Non-Equi Joins in SQL Server
You’ve used equi-joins so far, and you’ve probably never called them that way. The reason for that is that they are so common, and the whole idea of databases is related to joining tables in such a manner. So, what are equi-joins? Equi-joins are standard joins where you’ll use the equality operator (=) while joining tables. Calling such “standard” joins an equi-joins is just a fancy way to name it. This stands for joins where you join using FK, but also for joins where you compare attributes that are not part of a foreign key (this is rarely used). Let’s examine this on a few examples.
We’ll start with something very familiar. First, we’ll list the contents of tables city and country and then use the INNER JOIN to combine only cities and countries that are related (logically, but also with the FK value). We could have done the same using LEFT JOIN or RIGHT JOIN too.
SELECT * FROM city;
SELECT * FROM country;
INNER JOIN country ON city.country_id = country.id;
You can see the result of these 3 queries in the picture below.
Everything is as usual and as expected. All cities were joined to the counties they belong to. This is due to the fact we’ve joined tables on FK and used the equality sign in the join condition. So, this is an equi-join.
Now we’ll do the same using the non-equi join in SQL Server. First, we’ll join all cities and countries where the city doesn’t belong to that country. To do that, we’ll use the <> operator (you could have used the != operator too).
INNER JOIN country ON city.country_id <> country.id;
— pairs of cities
SELECT c2.id, c2.city_name, c1.id, c1.city_name
FROM city c1
INNER JOIN city c2 ON c1.id <> c2.id;