Learn SQL: Non-Equi Joins in SQL Server

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.

Data Model

On this part, nothing has changed since the last article, so we’ll use the same model we’re using throughout this series.

Non-Equi Joins SQL Server - the data model

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.

You can see the result of these 3 queries in the picture below.

select statements

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).

joining two tables using <> operator”></p>
<p><noscript><img src=

— 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;

Please note that this query is a SELF JOIN because we’ve used the city table twice (and joined it to itself). Also, notice that we’ve used alias names (c1 and c2) to distinguish these two table instances. Such cases are rare, and you’ll mostly use them when you want to create categories/pairs/ 2D matrix for any business reason.

The result is shown in the picture below.

joining two tables using <> operator”></p>
<p><noscript><img src=

— pairs of cities (only once)
SELECT c2.id, c2.city_name, c1.id, c1.city_name
FROM city c1
INNER JOIN city c2 ON c1.id > c2.id;

The query returns the following result.

joining two tables using > operator”></p>
<p><noscript><img src=foreign key. Joining tables in such manner is the point of databases, after all. In most cases, it’s not rational (correct, wise, use whatever word you want) to relate tables using values from logically unrelated attributes. Still, if you, for any reason, want (need) to do that, you can do it. We’ll examine that in one example now.

You can see the result in the picture below.

Non-Equi Joins SQL Server - example

We’ve joined cities and countries which have different PK value. Since PK value is automatically generated and therefore is unique to our database, this result set is not related to the real world in any way. While the output itself is nothing meaningful, it proves the point that we can use non-equi join in SQL Server also on different tables.

When to use Non-Equi Joins in SQL Server?

You’ll use them:

  • When someone tells you so 🙂 I know this doesn’t explain anything, and this is more like a self-fulfilling prophecy, but mostly, you’ll use them when someone wants to test if you know what they are (school, college, job interview, etc.). Of course, this stands for anything else related to databases (or programming)
  • When you need them, or they could ease your job a lot. OK, once more, this also stands for everything else, but for this bullet, we gave a few examples before. This mostly stands for generating categories and pairs combined with using self-join
  • We haven’t mentioned it here, but you could also check for duplicate data (similar to self-join), compute running totals (there is a much better way to do that than using non-equi joins in SQL Server), or match against a range of values

Conclusion

I hope that today’s article gave a brief but clear explanation of what non-equi joins in SQL Server are and when you should use them. They are rarely used, so when you decide to go with them, use them wisely. And stay tuned for upcoming articles.

Table of contents

Emil Drkusic
Latest posts by Emil Drkusic (see all)

Author: admin

Leave a Reply

Your email address will not be published.