CODEWITHSUNDEEP
sqlpostgresqlselectleft-join
Nick M
Nick M

Reputation: 2522

Simple Postgres query returning no result

I have two tables:

products(id: integer, price: float, website_id: integer)

and

adjustments(id: integer, product_id: integer, website_id: integer, factor:float)

I have indexes on id in both tables, and an index on website_id and product_id on the adjustments table.

This query returns data but it takes some 5 seconds with some 1k products and a couple hundred in adjustments:

select 
    products.id, 
    case
    when adjustments.website_id = 1
    then
        products.price+coalesce(adjstments.factor,0)
    else
        products.price
    end as price
from
    products
left join
    adjustments on products.id = adjustments.product_id
where
    products.website_id = 1;

This other query however returns no results when there's nothing in the adjustments table:

select 
    products.id, 
    products.price+coalesce(adjstments.factor,0) as price
from
    products
left join
    adjustments on products.id = adjustments.product_id
where
    products.website_id = 1 and adjustments.website_id = 1;

Was it not supposed to return the data from the first table regardless if there are any matching record in the 2nd table? What am I doing wrong?

Upvotes: 1

Views: 182

Answers (1)

Mureinik
Mureinik

Reputation: 311028

The filtering in the where clause if applied after the join. So here, you get all the rows from products, and match them with empty adjustments rows (since the table is empty). Then, you apply the where condition, that includes adjustments.website_id = 1 since adjustment.website_id is always null, no rows are returned.

You could move this condition to the join in order to get the behavior you expected:

select 
    products.id, 
    products.price+coalesce(adjstments.factor,0) as price
from
    products
left join
    adjustments on products.id = adjustments.product_id and adjustments.website_id = 1
where
    products.website_id = 1;

Upvotes: 1

Related Questions