Uses of unequal joins

Question

Of all the thousands of queries I've written, I can probably count on one hand the number of times I've used a non-equijoin. e.g.:

SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.date > tbl2.date

And most of those instances were probably better solved using another method. Are there any good/clever real-world uses for non-equijoins that you've come across?

Answer 1

Bitmasks come to mind. In one of my jobs, we had permissions for a particular user or group on an "object" (usually corresponding to a form or class in the code) stored in the database. Rather than including a row or column for each particular permission (read, write, read others, write others, etc.), we would typically assign a bit value to each one. From there, we could then join using bitwise operators to get objects with a particular permission.

Answer 2

Dublicates;

SELECT
  *
FROM
  table a, (
    SELECT
      id,
      min(rowid)
    FROM 
      table
    GROUP BY
      id
  ) b
WHERE
  a.id = b.id
  and a.rowid > b.rowid;

Answer 3

How about for checking for overlaps?

select ...
from   employee_assignments ea1
,      employee_assignments ea2
where  ea1.emp_id = ea2.emp_id
and    ea1.end_date >= ea2.start_date
and    ea1.start_date <= ea1.start_date

Answer 4

If you wanted to get all of the products to offer to a customer and don't want to offer them products that they already have:

SELECT
     C.customer_id,
     P.product_id
FROM
     Customers C
INNER JOIN Products P ON
     P.product_id NOT IN
          (
               SELECT
                    O.product_id
               FROM
                    Orders O
               WHERE
                    O.customer_id = C.customer_id
          )

Most often though, when I use a non-equijoin it's because I'm doing some kind of manual fix to data. For example, the business tells me that a person in a user table should be given all access roles that they don't already have, etc.

Answer 5

If you want to do a dirty join of two not really related tables, you can join with a <>.

For example, you could have a Product table and a Customer table. Hypothetically, if you want to show a list of every product with every customer, you could do somthing like this:

SELECT * FROM Product p JOIN Customer c on p.SKU <> c.SSN

It can be useful. Be careful, though, because it can create ginormous result sets.

Answer 6

Whole-day inetervals in date_time fields:

date_time_field >= begin_date and date_time_field < end_date_plus_1