Postgres LEFT JOIN with WHERE condition

by

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 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.

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).

More Related Contents:

  1. How to filter SQL results in a has-many-through relation
  2. Left Outer Join doesn’t return all rows from my left table?
  3. How to select the nth row in a SQL database table?
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Simple Random Samples from a Sql database
  7. SQL: Repeat a result row multiple times, and number the rows
  8. sql join two table
  9. Why do results from a SQL query not come back in the order I expect?
  10. Best practices for SQL varchar column length [closed]
  11. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  12. MySQL: How do I join same table multiple times?
  13. Are SELECT type queries the only type that can be nested?
  14. how to cast the hexadecimal to varchar(datetime)?
  15. What happens when I exhaust a bigint generated key? How to handle it?
  16. i have an error which i dont understand. can you help me? [closed]
  17. MySQL select 10 random rows from 600K rows fast
  18. Find nearest latitude/longitude with an SQL query
  19. Simulate lag function in MySQL
  20. Get record counts for all tables in MySQL database
  21. How do I check if a column is empty or null in MySQL?
  22. Use MySQL spatial extensions to select points inside circle
  23. What is this operator in MySQL?
  24. MySQL create stored procedure syntax with delimiter
  25. How to find non-existing data from another Table by JOIN?
  26. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  27. What is the difference between SQL and MySQL? [closed]
  28. how to use a like with a join in sql?
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. In what order are MySQL JOINs evaluated?

Leave a Comment