SQL: FROM table1, table2 + WHERE 2 conditions

Question

I have a table with dates and temperatures:

Id  Recorddate  Temperature
1   2015-01-01  10
2   2015-01-02  25
3   2015-01-03  20
4   2015-01-04  30

code for reproducibility:

CREATE TABLE IF NOT EXISTS temps(
    Id INT NOT NULL,
    Recorddate DATE NOT NULL,
    Temperature INT NOT NULL,
    PRIMARY KEY(Id)
);
 
INSERT INTO temps(Id,Recorddate,Temperature)
VALUES(1, '2015-01-01', 10),
      (2, '2015-01-02', 25),
      (3, '2015-01-03', 20),
    (4, '2015-01-04', 30) ;

The following code returns the list of dates that had a higher temperature than their previous day.

SELECT DISTINCT t1.Recorddate, t1.temperature
FROM temps t1, temps t2
WHERE t1.temperature > t2.temperature #first condition
    AND Cast((JulianDay(t1.Recorddate) - JulianDay(t2.Recorddate)) As Integer) = 1 #second condition

OUTPUT:

Recorddate  Temperature
2015-01-02  25
2015-01-04  30

I do not understand the logic behind this code. I understand the two conditions independently, and I am familiar with the join logic, but it is not helping here.

Here is the output with the first condition only: WHERE t1.temperature > t2.temperature

OUTPUT:

Recorddate  Temperature
2015-01-02  25
2015-01-03  20
2015-01-04  30

I understand that dates of t1 that have a temperature higher than at least another day in t2 are selected. Is that correct ?

As per the second condition, I do not understand how it articulates with the two tables and the first condition. I would appreciate if someone could break down the logic.

user_1144035 · Answer

Don't both learning how that code works. It uses archaic JOIN syntax (commas), which are simply unnecessary. A self join is not a good way to solve this problem anyway.

A much better approach -- both from understandability and performance -- is to use window functions:

select t.*
from (select t.*,
             lag(temperature) over (order by recordDate) as prev_temperature
      from temps t
     ) t
where temperature > prev_temperature ;

Note: This takes the previous date in the data. If you specifically want one day before, then there are various methods. One would be:

select t.*
from (select t.*,
             lag(temperature) over (order by recordDate) as prev_temperature,
             lag(recordDate) over (order by recordDate) as prev_recorddate
      from temps t
     ) t
where temperature > prev_temperature and
      julianday(prev_RecordDate) = julianday(RecordDate) - 1

Note: this is not a Standard SQL method for date comparisons. Based on your code, I assume it works in the database you are using.

SQL: FROM table1, table2 + WHERE 2 conditions

1 Answers