Postgres LEFT JOIN with WHERE condition

Question

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

Table 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

Table 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

What have you tried? Please post some sample code and output, and tell us what isn't working for you. Also, check this link out: stackoverflow.com/help/mcve. — CLAbeel
– CLAbeel, Commented Nov 7, 2016 at 12:34
Your output example does not require an outer join as in the question title. It is an inner join. — Clodoaldo Neto
– Clodoaldo Neto, Commented Nov 7, 2016 at 13:19
@ClodoaldoNeto: A LEFT JOIN makes sense to show the row(s) (or selected columns) from time_table matching rid = 2, even if no row in record_table overlaps with the time range. Else, if the user gets an empty result (no row), and (s)he can't tell whether there's no match in time_table (no rid = 2) or in record_table. Of course, it would make sense to include start_date and end_date in the result, or you just get a row of NULL values for the latter case - which would still be significant. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 8, 2016 at 0:43
@ErwinBrandstetter: Although it is possible to make the query complete the posted output example does not give a hint on that. The OP mentions left join but it could just be some misunderstanding. Without a clarification from him I tend to put more weight on the desired output. My comment above is a call for elucidation and I should have made it clear. But congratulations for your always detailed answers. — Clodoaldo Neto
– Clodoaldo Neto, Commented Nov 8, 2016 at 11:49

Erwin Brandstetter · Accepted Answer · 2024-01-31 12:10:30Z

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 on the right table, thereby voiding the special feature of a LEFT JOIN to include all rows from the left table unconditionally. Detailed explanation:

Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

Put conditions supposed to filter all rows into the WHERE clause (rid = 2), but move conditions on record_table to the join clause:

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.

Hambone · Accepted Answer · 2016-11-07 23:39:04Z

I'm not exactly sure if this is what you want, but if you are saying you want the dates where the record_table date is between the dates in the time_table, then this would do the job:

select
  rt.id, rt.name, rt.date
from
  time_table tt
  join record_table rt on
    rt.date between tt.start_date and tt.end_date
where
  tt.rid = 2

That said, this will be horribly inefficient for large datasets. If your data is relatively small (< 10k records in each table, post-filters), then it probably won't matter much, but if you would need to scale this concept, it would warrant knowing more about your data -- for example, do the dates, always round to the first of each month?

Again, from your example, I wasn't sure if this is what you meant by "get all those records which are present under given date range."

Mohammad Nurul Huda Rimon · Accepted Answer · 2016-11-07 13:15:26Z

-3

SELECT time_tbl.name,record_tbl.date 
FROM dbo.time_table AS time_tbl
     INNER JOIN record_table AS record_tbl
           ON time_tbl.id=record_tbl.id
WHERE(time_tbl.rid=2)

edited Nov 7, 2016 at 13:15

Mohammad Nurul Huda Rimon

2,5351 gold badge17 silver badges18 bronze badges

answered Nov 7, 2016 at 12:38

Jani Devang

1,10913 silver badges20 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

This is incorrect in almost every aspect. Wrong JOIN, incorrect condition, wrong result columns. That can happen, but most importantly: no explanation at all.

Collectives™ on Stack Overflow

Postgres LEFT JOIN with WHERE condition

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related