khajvah khajvah - 3 months ago 8
SQL Question

SQL LEFT JOIN empties out the left table columns

I came across some weird behavior today with postgresql.

WITH actual_prices AS (
-- Looking for prices from now to the given number of days back
SELECT *
FROM prices
WHERE price_date >= now()::date - 93
)
, distinct_products_sold AS (
SELECT distinct(id_product) as pid FROM products_sold
)
, first_prices AS (
SELECT s.pid, p.product_id, p.price_date, p.price
FROM distinct_products_sold s
LEFT JOIN actual_prices p ON p.product_id = s.pid
)
select * from first_prices;


This code outputs something of this kind:

129 | | |
195 | | |
251 | | |
...


In other words, columns of table
actual_prices
are empty. I tried messing around with
JOIN
just to see what's going on: if I do
RIGHT JOIN
instead of
LEFT JOIN
, it empties the column of
distinct_products_sold
but the columns of
actual_prices
are displayed correctly. What can cause this?

Answer

You have it the wrong way around: it is not that the outer join causes data to be lost from one table, rather it forces a union between the tables by padding the missing columns with nulls e.g.

WITH P ( PID ) AS
(
 SELECT *
   FROM (
         VALUES ( 1 ), ( 2 ), ( 3 )
        ) AS T ( C )
),
Q ( QID ) AS
(
 SELECT *
   FROM (
         VALUES ( 4 ), ( 5 ), ( 6 )
        ) AS T ( C )
)
SELECT p.PID, q.QID
  FROM P p
  INNER JOIN Q q
   ON p.PID = q.QID
UNION
SELECT p.PID, NULL
  FROM P p
 WHERE p.PID NOT IN ( SELECT QID FROM Q );
Comments