Greater/Lesser signs for ON Clause

• BabyAqua

  SSC Veteran

  Points: 263

  More actions

  July 17, 2017 at 11:23 pm

  #324535

  Usually, most joins use the equal sign with the ON clause.  For example:
  
SELECT * FROM
Table1 T1
INNER JOIN
Table2 T2
ON T1.Field3 = T2.Field3
AND T1.Field5 = T2.Field5


  However, once in a while, I will see the following example with a greater/lesser sign:
  
SELECT * FROM
Table1 T1
INNER JOIN
Table2 T2
ON T1.Field3 > T2.Field3
AND T1.Field5 = T2.Field5


  I have seen it used in prior to the existence of the function ROW_NUMBER to create an incremental field.  What exactly is the logic behind the greater/lesser sign, used in this manner?  Can the logic be look at as comparing current record to the previous/next record?

  And what about when it has a greater/lesser sign with an equal sign?

  Thanks

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 18, 2017 at 2:08 am

  #1951315

  With your statement T1.Field3 > T2.Field3, that part of your join will limit the rows to only those where the value of is T1.Field3 greater than T2.Field3. If you replaced > with < it would be the opposite.

  Sometimes, if you don't understand things, the easiest way is the test with some simple data. Take the following example:
  CREATE TABLE #Table1
  (LowerNum int,
  UpperNum int);
INSERT INTO #Table1
VALUES (1,10),(2,9),(3,7),(4,6),(5,5);

  CREATE TABLE #Table2
  (Num int);
INSERT INTO #Table2
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
GO

  SELECT T1.LowerNum, T2.Num, T1.UpperNum
FROM #Table1 T1
  JOIN #Table2 T2 ON T1.LowerNum < T2.Num AND T1.UpperNum > T2.Num;
GO

  DROP TABLE #Table1;
DROP TABLE #Table2;
GO
  The SELECT statement returns all the values in Table2 that are between (but not equal to) the values of in Table1.

  As for ROW_NUMBER, I'm really not sure what you mean here. ROW_NUMBER is used the the following format (and I can't think of any times you would have a > or < in the expression):
  ROW_NUMBER() OVER ({PARTITION BY [Columns]...} ORDER BY [Columns])

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk