Postgres Left Join with where condition

Syed Asad Abbas Zaidi picture Syed Asad Abbas Zaidi · Nov 7, 2016 · Viewed 11.5k times · Source

I need to left join two tables with a where condition:

time_table
id     rid        start_date                end_date
1       2     2017-07-01 00:00:00     2018-11-01 00:00:00
2       5     2017-01-01 00:00:00     2017-06-01 00:00:00
3       2     2018-07-01 00:00:00     2020-11-01 00:00:00
record_table
id      name                 date
1      record1       2017-10-01 00:00:00
2      record2       2017-02-01 00:00:00
3      record3       2017-10-01 00:00:00

I need to get all those records which are present under given date range. In the above example, I need those records that lie under range for rid = 2 only. Hence the output for the above query needs to be:

1      record1       2017-10-01 00:00:00    
3      record3       2017-10-01 00:00:00

Answer

Erwin Brandstetter picture Erwin Brandstetter · Nov 8, 2016

left join two tables with a where condition

It's typically wrong to use a LEFT [OUTER] JOIN and then filter with a WHERE condition, thereby voiding the special feature of a LEFT JOIN to include all rows from the left table unconditionally. Detailed explanation:

Put conditions supposed to filter all rows into the WHERE clause (rid = 2), but make conditions to left-join rows from record_table out to be actual join conditions:

SELECT t.start_date, t.end_date  -- adding those
     , r.id, r.name, r.date 
FROM   time_table t
LEFT   JOIN record_table r ON r.date >= t.start_date
                          AND r.date <  t.end_date
WHERE  t.rid = 2;

As commented, it makes sense to include columns from time_table in the result, but that's my optional addition.

You also need to be clear about lower and upper bounds. The general convention is to include the lower and exclude the upper bound in time (timestamp) ranges. Hence my use of >= and < above.

Related:

Performance should be no problem at all with the right indexes. You need an index (or PK) on time_table(rid) and another on record_table(date).