T-SQL 101: 110 Joins without equality (non-equi joins)

I’ve made several posts about joins but one option that I haven’t talked about as yet, are what are called non-equi joins.

These are less common, and they often don’t optimize all that well, but it’s worth noting that joins don’t always have to be on equality, or what are called equi-joins.

Non-equi join sounds complex but all we’re saying is that the things that we’re joining on are not equal signs.

Non-Equi Join Example

In the example above, I’ve started with the Orders table, but for each order, I want to count the number of orders that have lower OrderID values, and where the OrderDate is 1st January 2019 or later.

Performance Note

As I mentioned, these types of queries can lead to performance issues if they’re not designed very, very carefully. You can imagine that as you go through a list of these orders, the list that you’re joining to could well be getting bigger and bigger.

For that reason, you need to be a little careful with non-equi joins or when you have joints that are on greater than, less than, things like that, rather than just equals.

Later, we’ll discuss how window functions can often help with this type of query.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-06