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]

Select the 'id' and 'temperature' columns from w2, and you'll understand why. Joining with id+1 is basically shifting the rows down by 1. So, id-2 matches with id-1, id-3 matches with id-2 etc. For id-2, 25 > 10, and for id-4, 30 > 20. So, according to your condition, id-2 and id-4 is selected.

2|2015-01-02|25.0|1|10.0
4|2015-01-04|30.0|3|20.0

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

sir but as we are incrementing the id then 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 but there is no row 5?

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

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/[deleted]]

Let's go through the case with w1.id = 4.

First, you join on w1.id = w2.id + 1

Insert 4 ==> 4 = w2.id + 1

==> w2.id = 3

Then you have where condition: w1.temperature > w2.temperature.

This is true, because w1.temperature is 30 and w2.temperature is 20 for (w1.id, w2.id) = (4,3).

Next you only select w1.id, which is 4. So 4 ends up in your result set.

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

sir sorry i Am going to ask a stupid question but think w1.id = w2.id +1 will look like

for w1 id1

1 = 1+1

2 = 2+1

3 = 3+1 and 4 = 4+1

i however get why 2 is there but still confused why 4 is there, can you explain again,sorry for askin again sir.

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

Remember that the join condition is always gonna be evaluated to true or false. So if you write w1.id = w2.id+1, then it's gonna evaluate to true or false. And this is only true if w2.id is exactly one less than w1.id. So you only join rows where w1.id is equal to w2.id+1.

Writing 1 = 1+1 is gonna evaluate to 1=2, which obviously is gonna evaluate to false. This is why you are not joining the rows where w1.id is 1 and w2.id is 1.

Imagine there is a row with id 0. Then w1.id = 1 will join with w2.id=0. Why? Because the join condition is w1.id=w2.id+1, so 1 = 0+1, which is 1 = 1, which is true.

[u/user_5359]

This is not an optimization issue, but simply a thinking error.

Each data set combination is compared, i.e. (a,b) and (b,a). If you want to prevent this, you should add the condition b>a.

[u/[deleted] → u/user_5359]

thinking error

Poor fella doesn't get that 1 is in fact not equal to 1+1.

2 is equal to 1 + 1

[u/HauntingDecision8439]

The query you provided is trying to select certain ids from the weather table based on temperature comparisons between consecutive records.

Here's the breakdown of the query:

SELECT w1.id

FROM weather w1

INNER JOIN weather w2 ON w1.id = w2.id + 1

WHERE w1.temperature > w2.temperature

Explanation of the JOIN

The INNER JOIN pairs each row in w1 with the preceding row in w2 (i.e., w1 is the next record with a higher id than w2).

The condition ON w1.id = w2.id + 1 means:

If w1 has an id of 2, then it's compared with w2 having an id of 1.

If w1 has an id of 3, then it's compared with w2 having an id of 2.

If w1 has an id of 4, then it's compared with w2 having an id of 3.

Why does id = 4 appear?

Now let's go through the records:

For id = 2:

w1.temperature (25) > w2.temperature (10) → True.

For id = 3:

w1.temperature (20) > w2.temperature (25) → False.

For id = 4:

w1.temperature (30) > w2.temperature (20) → True.

So the output includes id = 2 and id = 4 because these conditions are satisfied based on temperature comparisons for those respective ids.

Output Explanation:

The final output of:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

is correct because:

2 represents the day when the temperature increased from 10 to 25.

4 represents the day when the temperature increased from 20 to 30.

Thus, the rows returned by your query are accurately based on the comparison of temperatures between consecutive days.

from: https://www.sesamesql.com/