Back to articles list June 9, 2020 - 9 minutes read Practical Examples of When to Use Non-Equi JOINs in SQL Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql joins 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 iddistrictaddressbedroomsrent 1SouthRose Street, 543000.00 2NorthMain Street, 1232250.00 3SouthRose Street, 543000.00 4WestNice Street, 321750.00 5WestPark Avenue, 1043500.00 6SouthLittle Street, 743000.00 7NorthMain Street, 832100.00 Renters idnamepreferred_districtmin_bedroomsmin_rentmax_rent 1Helen BossSouth32500.003200.00 2Michael LaneWest21500.002500.00 3Susan SandersWest42500.004000.00 4Tom WhiteNorth32200.002500.00 5Sofia BrownNorth31800.002300.00 Deals iddaterenter_idhouse_idagent_fee 12020-01-3011600.00 22020-02-0324350.00 32020-03-1235700.00 42020-04-1042450.00 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. namepreferred_distirctnamepreferred_distirct Michael LaneWestSusan SandersWest Susan SandersWestMichael LaneWest Tom WhiteNorthSofia BrownNorth Sofia BrownNorthTom WhiteNorth 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; namepreferred_distirctnamepreferred_distirct Michael LaneWestSusan SandersWest Tom WhiteNorthSofia BrownNorth 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. idaddressidaddress 1Rose Street, 53Rose Street, 5 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): idnameidaddressrentbedrooms 1Helen Boss6Little Street, 730004 1Helen Boss3Rose Street, 530004 5Sofia Brown7Main Street, 821003 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. dateagent_feetotal_agent_fee 2020-01-30600.00600.00 2020-02-03350.00950.00 2020-03-12700.001650.00 2020-04-10450.002100.00 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'; idaddressdate 5Park Avenue, 102020-03-12 2Main Street, 122020-04-10 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'; idaddressdate 5Park Avenue, 102020-03-12 2Main Street, 122020-04-10 6Little Street, 7NULL 4Nice Street, 3NULL 1Rose Street, 5NULL 3Rose Street, 5NULL 7Main Street, 8NULL 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! Tags: sql learn sql joins You may also like An Illustrated Guide to the SQL Non Equi Join SQL join doesn’t have to be based on identical matches! In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records. Read more SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more SQL Basics Cheat Sheet This 2-page SQL Basics Cheat Sheet will be a great value for beginners as well as for professionals. Download it in PDF or PNG format. Read more SQL JOIN Types Explained Learn how to join tables in SQL. Review the different SQL join types and when to use inner join, left join, right join, or full join. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.