![]() ![]() 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.Īnd 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): id Whether the number of bedrooms satisfies the minimum requirements (non-equality condition to check for capacity).Whether the rent is within the renter’s acceptable range (non-equality condition with the range of values).Whether the house’s district corresponds to the renter’s preferred district (equality condition).Here, in the JOIN condition, we checked for the first three conditions mentioned above: WHERE h.id NOT IN (SELECT house_id FROM deals) ON r1.preferred_district = r2.preferred_district AND r1.id = or = r.min_bedrooms This very small modification of our query is a useful trick: It would make more sense to have only unique pairs in our result. The result looks pretty good except that we have the same pairs of renters listed twice in our table. The second condition with the != operator makes this JOIN a non-equi JOIN. The comparison operator != ensures that the result will include all possible pairs of renters, except for pairing the renters with themselves. ![]() The standard equal sign makes sure we pair only the customers with the same preferred district.We used two conditions with different comparison operators in our JOIN statement: ON r1.preferred_district = r2.preferred_district AND r1.id != r2.id Īs you can see in this example, we joined the renters table with itself. ![]() SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district 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. Let’s imagine that our renters are ready to consider sharing a house with another family. We’re going to start with the most common use cases for non-equi JOINs. 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. 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. ![]() Now, we are ready to move to non-equi JOIN examples.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |