Practical Examples of When to Use Non-Equi JOINs in SQL

If you think that two tables in SQL can be joined only using the common field, you will be excited to learn that there are other ways of joining. Non-equi JOINs use different kinds of comparison operators in the JOIN condition. In this article, I’ll guide you through non-equi JOIN examples to show the common use cases for this type of JOIN.

Equi JOIN vs. Non-Equi JOIN

How do you usually join two tables in SQL? Most likely, you select the common field in these two tables and join them using the equal sign in the join condition. For example, you can match the product ID from the product table with the product ID from the order table or the last name from the employee table with the last name from the timesheet.

In these cases, you are using an equi JOIN, which is just a fancy name for a join with an equal sign in the join condition. A lot of SQL beginners use equi JOINs and don’t even know that you can use a non-equality condition in JOIN.

Such joins are called non-equi JOINs, and they are also possible in SQL. When you join two tables using other conditional operators, beyond the equal sign, non-equi JOINs come into play. Comparison operators, like <, >, <=, >=, !=, and <> and the BETWEEN operator work perfectly for joining tables in SQL.

Check out this illustrated guide to the SQL non-equi JOIN for a better understanding of how it works.

Getting to Know the Data

Before diving into different examples of non-equi JOINs, let’s first see the data that we’ll be exploring in this article. We’re going to do some data analytics for a real estate agency that runs a rental business. We have three tables in our imaginary database:

• houses with the house ID, district, address, number of bedrooms, and rent
• renters with the renter ID, name, preferred district, minimum number of bedrooms required, and acceptable rent range
• deals with the deal ID, date, renter ID, house ID, and agent fee received from the corresponding deal

See these three tables below.

Houses

Renters

Deals

Now, we are ready to move to non-equi JOIN examples.

Use Cases for Non-Equi JOINs

If you have never used non-equi JOINs before, you may wonder what the common scenarios for applying this non-standard type of JOIN are. In fact, there are quite a lot of them. You can use non-equi JOINs to list all (unique) pairs of items, identify duplicates, list items within a certain range of values or between certain dates, compute running totals, and more.

We’re going to start with the most common use cases for non-equi JOINs.

Listing Pair Combinations

Let’s imagine that our renters are ready to consider sharing a house with another family. Thus, we want to list all possible pairs of our renters along with their preferred district to see which renters could potentially rent a house together. Here’s the SQL query you could use:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id != r2.id;

As you can see in this example, we joined the renters table with itself. We used two conditions with different comparison operators in our JOIN statement:

• The standard equal sign makes sure we pair only the customers with the same preferred district
• The comparison operator != ensures that the result will include all possible pairs of renters, except for pairing the renters with themselves

The second condition with the != operator makes this JOIN a non-equi JOIN.

The result looks pretty good except that we have the same pairs of renters listed twice in our table. It would make more sense to have only unique pairs in our result. This very small modification of our query is a useful trick:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id < r2.id;

By changing the comparison operator in the second join condition from != to <, we are listing only the pairs in which the ID value of the first renter is smaller than the ID value of the second renter. Thus, we now have only the row with Michael Lane (ID 2) listed in the first column and Susan Sanders (ID 3) listed in the third column, and not the row where Susan Sanders comes first.

To list all (unique) pairs of customers, we joined the renters table with itself, which is basically a self JOIN. For more about self JOINs, see this comprehensive guide with easy-to-follow examples.

Identifying Duplicates

Another common application of non-equi JOINs is to find duplicates in a dataset. For example, let’s say we want to check whether our houses table includes any duplicates, i.e., houses with the same address but different IDs.

Our query will be very similar to the one we used for listing unique pairs, but this time, we will self-join the houses table:

SELECT h1.id, h1.address, h2.id, h2.address
FROM houses h1
JOIN houses h2
ON h1.address = h2.address AND h1.id < h2.id;

We again have two join conditions: (1) to check whether the address is the same, and (2) to make sure we list only unique pairs with different IDs.

The table above shows that there is one duplicate in our data set. The house located on Rose Street, 5 is mentioned two times in the table, with ID 1 and ID 3.

Joining Tables Using a Range of Values

Other popular applications of non-equi JOINs include joining two tables using:

• The BETWEEN operator to check if a certain value/date falls into a specified range
• Comparison operators like >= or <= to check for capacity

To see how these non-equi JOINs work in practice, let’s list other houses that we can suggest to our renters as an alternative. These should be houses (1) in their preferred district, (2) within their price range, (3) with their required number of bedrooms, and (4) not occupied (i.e., not listed in our deals table). Here’s the SQL query we can use:

SELECT r.id, r.name, h.id, h.address, h.rent, h.bedrooms
FROM renters r
JOIN houses h
ON h.district = r.preferred_district
    AND h.rent BETWEEN r.min_rent AND r.max_rent
    AND h.bedrooms >= r.min_bedrooms
WHERE h.id NOT IN (SELECT house_id FROM deals);

Here, in the JOIN condition, we checked for the first three conditions mentioned above:

• Whether the house’s district corresponds to the renter’s preferred district (equality condition)
• Whether the rent is within the renter’s acceptable range (non-equality condition with the range of values)
• Whether the number of bedrooms satisfies the minimum requirements (non-equality condition to check for capacity)

Then, in the WHERE statement, we used a subquery to filter only the houses that are presumably free, i.e., not yet in our deals table.

And here is the list of houses that we can suggest to our customers (note that the house with ID 3 is just a duplicate of the house that this customer is renting now):

Get more practice with these types of SQL non-equi JOINs in our comprehensive course on SQL JOINs.

Advanced Scenarios for Using Non-Equi JOINs

In addition to the common applications of non-equi JOINs mentioned above, there are some more advanced scenarios for applying these kinds of JOINs. Let’s dive into two examples.

Computing Running Totals

A non-equi JOIN can be used to compute a running total of a particular column. For example, let’s say that after every completed deal, we want to know the total agent fee received so far. Here is the SQL query we can use:

SELECT d1.date, d1.agent_fee, SUM(d2.agent_fee) AS total_agent_fee
FROM deals d1
JOIN deals d2
ON d1.date >= d2.date
GROUP BY d1.agent_fee, d1.date
ORDER BY d1.date;

We self-joined the deals table and used a non-equi JOIN condition to sum up all the agent fees received up until the date of the deal. Here is the result.

Please note that a better way to compute a running total is by using window functions. Our corresponding guide explains what a running total is and how to write an SQL query to compute it. For practice, see the LearnSQL.com interactive course "Window Functions".

Solving Conflicts Between LEFT JOINs and WHERE

Non-equi JOINs can also be handy in some situations when LEFT JOIN combined with the WHERE statement doesn’t work as intended. In particular, it’s often the case that the WHERE condition “cancels” the LEFT JOIN, making it work as an INNER JOIN instead.

For example, let’s say we want to list all houses from our database together with the date of the corresponding deal if it happened. We also want to consider only those deals that took place after March 1st.

Since we are interested in listing all houses, no matter if they have a matching deal, we will use a LEFT JOIN in our SQL query. We will also add a WHERE condition to consider only deals completed after a certain date:

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id
WHERE d.date >= '2020-03-01';

As you can see, the result is not exactly what we wanted. The table includes only the houses that were rented after March 1st instead of all houses.

A solution is to move the WHERE condition to ON and make it a JOIN condition. This will be a non-equi JOIN as it uses a >= comparison operator.

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id AND d.date >= '2020-03-01';

Now we see all houses in the result, even if they don’t have matching deals.

Time to Practice Non-Equi JOINs!

You have learned that non-equi JOINs can be very helpful in different scenarios. You can list unique pairs within one table, identify duplicates, join tables using a range of values and dates, compute running totals without using window functions, and more.

Now, what is the best way to practice SQL JOINs? Definitely, by writing code. So, let’s move on to interactive exercises!

The LearnSQL.com SQL JOINs course covers different JOIN types, including non-equi JOINs. You’ll have the opportunity to practice non-equi JOINs in various use cases. Furthermore, the comprehensive SQL Basics course offers additional practice on non-equi JOINs when explaining querying of more than one table and exploring more in-depth topics on JOINs.

Happy learning!