Using calculations in Joins

Hi,

I'm trying to understand why in certain cases, folks use calculations on Joins i.e comparing the primary key and then subtracting 1 in the join condition. As an example,

Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

Write an SQL query to find all numbers that appear at least three times consecutively.

``````SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
``````

Why do we use I2.Id - 1 in this case. What is the -1 part in this l1.Id = l2.Id - 1 statement doing?

Consumable test data which you should have provided:

``````CREATE TABLE #t
(
id int NOT NULL PRIMARY KEY
,num int NOT NULL
);

INSERT INTO #t
VALUES (1, 1), (2, 1), (3, 1), (4, 2)
,(5, 1), (6, 2), (7, 2);``````

Using modern JOIN syntax, you can see that it joins with the previous id so that 3 consecutive numbers can be compared.

``````SELECT L1.num AS Num1, L2.num AS Num2, L3.num AS Num3
--SELECT DISTINCT L1.num
FROM #t L1
JOIN #t L2
ON L1.id = L2.id - 1
JOIN #t L3
ON L2.id = L3.id - 1
--WHERE L1.num = L2.num
--	AND L2.num = L3.num;``````

It will be more efficient not to self join but to use the LAG function. This will also work if there is a gap in the id.

``````WITH Nums
AS
(
SELECT num AS Num1
,LAG(num) OVER (ORDER BY id) AS Num2
,LAG(num, 2) OVER (ORDER BY id) AS Num3
FROM #t
)
SELECT DISTINCT Num1
FROM Nums
WHERE Num1 = Num2
AND Num2 = Num3;``````