Using unequal joins

Question

Of all the thousands of queries that I wrote, I can probably count, on the one hand, the number of times I used non-equijoin. eg:.

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

And most of these instances were probably better solved using another method. Are there any good / smart ways to use in the real world for non-equijoins that you come across?

Answer 1

. "" ( ), . , (, , , ..), . .

Answer 2

?

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 3

inetervals date_time:

date_time_field >= begin_date date_time_field < end_date_plus_1

Answer 4

MCTS 70-433 (SQL Server 2008 Development Kit). .

, . ( - db AdventureWorks):

select
    SH3.SalesPersonID,
    SH3.OrderDate,
    SH3.DailyTotal,
    SUM(SH4.DailyTotal) RunningTotal
from
    (select SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
    from Sales.SalesOrderHeader SH1
    where SH1.SalesPersonID IS NOT NULL
    group by SH1.SalesPersonID, SH1.OrderDate) SH3
join
    (select SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
    from Sales.SalesOrderHeader SH1
    where SH1.SalesPersonID IS NOT NULL
    group by SH1.SalesPersonID, SH1.OrderDate) SH4
on SH3.SalesPersonID = SH4.SalesPersonID AND SH3.OrderDate >= SH4.OrderDate
group by SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal
order by SH3.SalesPersonID, SH3.OrderDate

, . SalesPersonID , . , , .

" " SH4.

Answer 5

;

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 6

, , , :

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
          )

, non-equijoin, - . , , , , ..

Answer 7

, < > .

, Product Customer. , , - :

SELECT * FROM Product p    c p.SKU < > c.SSN

This may be helpful. However, be careful because it can create gigantic result sets.