Back to articles list September 24, 2017 - 8 minutes read An Illustrated Guide to the SQL Non Equi Join Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: join SQL basics SQL clauses Did you know that in SQL, a join doesn’t have to be based on identical matches? In this post, we look at the SQL non equi join, which uses ‘non-equal’ operators to match records. We’ve already discussed several types of joins, including self joins and CROSS JOIN, INNER JOIN and OUTER JOIN. These types of joins typically appear with the equals sign (=). However, some joins use conditions other than the equals (=) sign. This is an example of a non equi join in SQL, and it’s what we will talk about in this article. What Is a Non Equi Join in SQL? If you’ve been reading the other posts in this series, you know how to use SQL joins to combine records based on exact value matches. But what if you are looking for a record that doesn’t need an exact match? Suppose you want to join records based on a range of values. Or maybe you want all the records that do not match some value. In these cases, you need to use a SQL non equi join. Like a self join, a SQL non equi join doesn’t have a specific keyword; you’ll never see the words NON EQUI JOIN in anyone’s SQL code. Instead, they are defined by the type of operator in the join condition: anything but an equals sign means a non equi join. As you’ll see in the box below, sometimes an equals sign is part of the operator. But in a SQL non equi join, it’s never the entire operator by itself. Below, we have some non equi join operators and their meanings: Operator Meaning “>” Greater than “>=” Greater than or equal to “<” Less than “<=” Less than or equal to “!=” Not equal to ”<>” Not equal to (ANSI Standard) BETWEEN … AND Values in a range between x and y Finally, it’s good to know that a SQL non equi join can only be used with one or two tables. Getting to Know Our Data Before we start describing non equi joins, let’s familiarize ourselves with some of the data we’ll be using. We’ll also review equi joins. Below we have the “person” table, which holds records for people who are involved in buying an apartment (i.e. a condo). All its columns are self-explanatory, but notice the “min_price” and “max_price” columns. These are the person’s price range for an apartment. The “apartment_id” column will link this table to the “apartment” table. id first_name last_name rooms min_price max_price apartment_id 1 Anne Miller 2 40,000 150,000 2 2 John Harris 1 20,000 50,000 2 3 Michael Moore 2 200,000 300,000 6 4 Oliver Watson 4 30,000 100,000 7 The “apartment” table stores information about apartments’, number of rooms, and city. Notice the values in the “id” column are basically the same as in the “apartment_id” column above. id rooms price city 1 2 30,000 Houston 2 2 45,000 Dallas 3 3 125,000 Chicago 4 5 245,000 Los Angeles 5 4 340,000 San Jose 6 4 220,000 San Diego 7 1 36,000 Cleveland Now let’s review equi joins. What Is a SQL Equi Join? The majority of SQL joins are equi joins. An equi join is any JOIN operation that uses an equals sign and only an equals sign. You will see queries that use more than one join condition; if one condition is an equals sign and the other isn’t, that’s a considered a non equi join in SQL. As we’ve said before, equi joins need an exact match between two columns. Have a look at the query below: SELECT first_name, last_name, price, city FROM person JOIN apartment ON apartment.id = person.apartment_id ; This query selects the first and last name of the client, the price of the apartment, and the city in which the apartment is located. We used a JOIN (a.k.a. an INNER JOIN) to combine data from the “person” and “apartment” tables. This join only shows records that can be matched in both tables. In the joining condition, we used the equals operator on the “apartment_id” column in the “person” table and the “id” column in the “apartment” table to find an exact match. The resulting table contains the following rows: first_name last_name price city Anne Miller 30,000 Houston John Harris 45,000 Dallas Michael Moore 220,000 San Diego Oliver Watson 36,000 Cleveland We’ve seen how an equi join works. Let’s dig into SQL non equi joins. Using a SQL Non Equi Join with Two Tables SELECT first_name, last_name, min_price, max_price, price, city FROM person JOIN apartment ON apartment.id != person.apartment_id AND price BETWEEN min_price AND max_price ORDER BY last_name; We used the JOIN operator to combine records from the “person” and “apartment” tables. We selected the first and last name of each person, the minimal and maximal desired prices, and the price and city of all apartments not chosen by the client. We used the BETWEEN … AND operator to match apartment prices. We also used the “!=” operator in a condition with “apartment_id” from the “person” table and “id” from the “apartment” table. By doing so, we removed the apartment that was chosen from the result table. The result table looks like this: first_name last_name min_price max_price price city John Harris 20,000 50,000 30,000 Houston John Harris 20,000 50,000 36,000 Cleveland Anne Miller 40,000 150,000 125,000 Chicago Michael Moore 200,000 300,000 245,000 Los Angeles Oliver Watson 30,000 100,000 45,000 Dallas Oliver Watson 30,000 100,000 30,000 Houston The Dallas apartment chosen by John Harris was not shown. Note that he might have chosen a flat in Houston ($30,000) or in Cleveland ($36,000). Both of these apartments fit into his price range of $20,000 – $50,000. SQL Non Equi Join in Self Join Now let’s see how a SQL non equi join works when a table is joined to itself. Our examples will be based on the “playing_cards” table shown below. It contains the following columns: “id” (an internal identifier), “rank” (the rank or face value of the card), and “suit” (the suit of the card). id rank suit 1 A Hearts 2 A Spades 3 A Clubs 4 K Spades 5 K Diamonds 6 Q Clubs 7 J Spades These are the cards stored in the “playing_cards” table: Note that we only have seven cards in the set. Let’s look at three ways we can use non equi joins in these circumstances. 1. Finding All Card Pairs Based on the set of cards in the “playing_cards” table, we will find all possible pairs of cards. Take a look at the query: SELECT c1.rank, c1.suit, c2.rank,c2.suit FROM playing_cards c1 JOIN playing_cards c2 ON c1.id != c2.id ORDER BY c1.rank; In the query above, we joined the “playing_cards” table to itself using the aliases c1 and c2. (For more about self joins and aliases, see our previous article.) We then used the inequality operator “!=”, creating a SQL non equi join, to obtain all possible card pairs. This operator shows pairs of cards in varying order and removes pairs of identical cards at the same time. The table below shows some of the resulting rows. We’ve highlighted rows containing duplicate pairs. rank suit rank suit A Spades A Hearts A Spades A Clubs A Spades K Spades A Spades K Diamonds A Spades Q Clubs A Spades J Spades A Clubs A Hearts A Clubs A Spades A Clubs K Spades A Clubs K Diamonds A Clubs Q Clubs A Clubs J Spades We see the first 12 of 42 total records. The picture below shows these selected pairs: 2. Eliminating Duplicate Pairs of Cards The pairs of cards returned by the last query were not unique because the query result included pairs shown in reverse order, i.e. “Ace of Spades with Ace of Clubs” and “Ace of Clubs with Ace of Spades”. In the next query, we will only return unique pairs. Cards’ position in the pair does not matter. SELECT c1.rank, c1.suit,c2.rank,c2.suit FROM playing_cards c1 JOIN playing_cards c2 ON c1.id < c2.id ORDER BY c1.rank; As you see, we used a SQL non equi join with the “<” operator instead of “!=”. As a result, we obtained unique card pairs. And these are the results: rank suit rank suit A Spades A Hearts A Clubs A Hearts A Clubs A Spades J Spades A Hearts J Spades A Spades J Spades A Clubs J Spades K Spades J Spades K Diamonds J Spades Q Clubs K Spades A Hearts K Spades A Spades K Spades A Clubs K Diamonds A Hearts K Diamonds A Spades K Diamonds A Clubs K Diamonds K Spades Q Clubs A Hearts Q Clubs A Spades Q Clubs A Clubs Q Clubs K Spades Q Clubs K Diamonds In this case, 21 records matched the condition, and all resulting pairs appeared only once. 3. Finding Pairs of Cards with the Same Suit In the next query, we will select unique pairs of cards with the same suit (Hearts, Spades, Clubs, and Diamonds). Can you find the non equi operator we used? SELECT c1.rank, c1.suit,c2.rank,c2.suit FROM playing_cards c1 JOIN playing_cards c2 ON c1.rank < c2.rank AND c1.suit = c2.suit ; The query above used two conditions: one that compares the cards’ “rank” and another that compares the “suit” column. Take a look at the query result. rank suit rank suit A Spades K Spades J Spades K Spades A Clubs Q Clubs A Spades J Spades Only four records matched the JOIN conditions. The situation is illustrated below. Learn More About SQL You now know what a SQL non equi join is and how it works. If you are interested in learning more about SQL, check out LearnSQL.com’s courses. You can practice your new skills with interactive exercises. You can also learn more about SQL joins in our posts, SQL Joins and Learning SQL JOINs Using Real-Life Situations. Tags: join SQL basics SQL clauses You may also like An Illustrated Guide to Multiple Join Did you wonder what is the most efficient way of making multiple joins in SQL? We did as well - come by and check out our illustrated guide! Read more An Illustrated Guide to the SQL CROSS JOIN CROSS JOIN returns a Cartesian product, or all records joined to all records in all tables. Take a look at the example queries. Read more An Illustrated Guide to the SQL INNER JOIN INNER JOIN combines data from multiple tables by joining them based on a matching record. It requires a JOIN condition, which we'll explain in this article. Read more An Illustrated Guide to the SQL OUTER JOIN An SQL JOIN clause links the data from two or more database tables. Let’s find out what is the OUTER JOIN and how does it work! Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more The 5 Highest Paying Jobs That Use SQL Every year, many people look and aim for new & better jobs. SQL-related jobs are some of the most promising. Discover the top 5 highest SQL job salaries! Read more How to Track Down Duplicate Values in a Table Duplicates are one of the most common challenges to data quality. Let's see how it is possible to find & distinguish duplicate names with the help of SQL. Read more Common SQL Window Functions: Positional Functions Positional SQL window functions deal with data's location in the set. In this post, we explain LEAD, LAG, and other positional functions. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.