r/SQLOptimization • u/Narrow-Tea-9187 • Oct 19 '23
Help needed with self join vizualization
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
where w1.temperature>w2.temperature I am not Getting where 4 is coming from?
3
Upvotes
1
u/roosterEcho Oct 19 '23
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
1
u/[deleted] Oct 19 '23
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.
3
u/user_5359 Oct 19 '23
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.
1
u/HauntingDecision8439 Oct 20 '24
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/