left join with condition for right table in mysql

Question

I have been trying to wrap my head around this issue but I am not making much progress. My goal is to do a left join between two tables with criteria for the right table. I would like to see the list of all products and prices for the current day even though there is no pricing row for the current day. Here is an example of the code:

SELECT products.id, products.name, prices.price
FROM products LEFT JOIN prices
ON products.id = prices.id
WHERE prices.date = CURRENT_DATE

This produces only the products with price information for the current date.

OK, so that is just the first part of my issue. I would eventually like to pull the price for CURRENT_DATE + INTERVAL 1 DAY as well.

Any information would be greatly appreciated.

score 14 · Accepted Answer · answered Oct 23 '10 at 04:12

Assuming PRICES.date is a DATETIME data type, use:

   SELECT pd.id, 
          pd.name, 
          pr.price
     FROM PRODUCTS pd
LEFT JOIN PRICES pr ON pr.id = pd.id
                   AND DATE(pr.date) = CURRENT_DATE

I used the DATE function to remove the time portion, because CURRENT_DATE won't include the time portion while DATETIME records will.

In this example, the date criteria is being applied before the JOIN is made. It's like a derived table, filtering down the information before the JOIN is made -- which'll produce different results than if the criteria was specified in the WHERE clause.

To get the list of products and prices for tomorrow, use:

   SELECT pd.id, 
          pd.name, 
          pr.price
     FROM PRODUCTS pd
LEFT JOIN PRICES pr ON pr.id = pd.id
                   AND DATE(pr.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)

Reference:

DATE_ADD

If you want both todays and tomorrows prices in a single query, use:

   SELECT pd.id, 
          pd.name, 
          pr1.price AS price_today,
          pr2.price AS price_tomorrow
     FROM PRODUCTS pd
LEFT JOIN PRICES pr1 ON pr1.id = pd.id
                   AND DATE(pr1.date) = CURRENT_DATE
LEFT JOIN PRICES pr2 ON pr2.id = pd.id
                   AND DATE(pr2.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)

The key reason this solution works is that the condition on the right table is put in the join criteria, so the left table's entry is returned even when the condition is not satisfied for any joined records. People often think of join criteria as relating columns in one table to columns in another, but they can also relate columns in one to a given constant or constraint as here. I can't emphasize enough how useful this general construction is, for me it comes up again and again and it is often an elegant and efficient way to return exactly the data you need without resorting to subqueries. — cazort, Jun 30 '21 at 15:18

score 8 · Answer 2 · answered Oct 23 '10 at 04:33

8

Strong answers above. Adding to OMG Ponies' reply... having 'right' table criteria in the original WHERE statement basically turns the LEFT OUTER JOIN into an INNER JOIN. Just a different way of looking at it that might help.

answered Oct 23 '10 at 04:33

bkj123

81
1

score 1 · Answer 3 · answered Oct 23 '10 at 04:13

SELECT id,
       name,
       (SELECT price
          FROM prices
         WHERE id = products.id
           AND prices.date = CURRENT_DATE
       ) AS price,
       (SELECT price
          FROM prices
         WHERE id = products.id
           AND prices.date = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)
       ) AS price_tomorrow
  FROM products

left join with condition for right table in mysql

3 Answers3

Linked