An Illustrated Guide to the SQL Non Equi Join

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:

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.

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.

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:

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:

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

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.

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:

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.

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.