Items from last month AND if not possible, items from this month
Use LEFT JOIN LATERAL ... ON TRUE to preserve all rows of the right table:

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN LATERAL (
   SELECT price, date
   FROM   log
   WHERE  article = i.article
   AND    date &lt; (i.date - 25)  -- make expression sargable, plus fix off-by-1 error
   ORDER  BY date DESC          -- to make sure ...
   LIMIT  1                     -- ... we pick at most 1 row
   ) l ON TRUE
ORDER  BY i.article, l.date;


Returns all rows in items, and you get NULL for price_difference where no previous older than 25 days is found in log.


What is the difference between LATERAL and a subquery in PostgreSQL?


I added ORDER BY ... LIMIT 1 to make sure we only pick the one latest entry. If data in table log can be trusted to never return more than one row, you can skip the added lines.

Or, if the query does not actually depend on items.date, a single subquery depending on the current date using DISTINCT ON can be cheaper (with the right indexes):

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN (
   SELECT DISTINCT ON (article)
          article, date, price
   FROM   log
   WHERE  date &lt; (CURRENT_DATE - 25)
   ORDER  BY article, date DESC
   ) l USING (article)
ORDER  BY i.article, l.date;


About DISTINCT ON:


Select first row in each GROUP BY group?