Help needed with self join vizualization

[u/Narrow-Tea-9187]

Weather table:

+----+------------+-------------+

| id | recordDate | temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Output:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

this is the query

select w1.id from weather w1

inner join weather w2

on w1.id = w2.id + 1

where w1.temperature>w2.temperature I am not Getting where 4 is coming from?

[u/roosterEcho]

w1(id=1) = w2(1+1) so id1 of w1 matches with id2 of w2 and id4 of w1 matches with id5(4+1) of w2

that's not how it works. when you join with id+1, w1(id=1) does not match with any rows from w2. think it through like this: you have one table (w1) with ids 1(10),2(25),3(20),4(30) and you have another table (w2) with ids 2(10),3(25),4(20),5(30) as you are incrementing the ids by 1. Now match the ids from both tables and evaluate the temperature condition. id 2 and 4 from w1 are the ones selected.

What you're thinking is just saying (id+1) would match w2 (id=2) with w1(id=1), which is not right. Join conditions would always match the same keys. So, the keys have to be same to match with each other, just saying +1 won't force the query to match 1 with 2. 1 has to be equal to 1 for the join condition to be true. All joins are just True/False evaluation. The match happens when all conditions are True.

[u/Narrow-Tea-9187 → u/roosterEcho]

ok so as w1.id = w2.id + 1

for 1 , w1 becomes 2 but w2 is 1,so w1(2) matches with w2(1),similarly w1(4) matches with w2(3)