我有两个表:
products(id: integer, price: float, website_id: integer)
和
adjustments(id: integer, product_id: integer, website_id: integer, factor:float)
我在两个表中都有关于ID的索引,在调整表上有对website_id和product_id的索引。
该查询返回数据,但是大约需要1千个产品和几百次调整的5秒:
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;
但是,当调整表中没有任何内容时,此其他查询将不返回任何结果:
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;
是否不应该从第二个表中返回数据,而不管第二个表中是否有任何匹配记录?我究竟做错了什么?
where
子句中的过滤(如果在联接之后应用)。因此,在这里,您从获得所有行products
,并将它们与空adjustments
行匹配(因为表为空)。然后,您应用where
条件,其中包括adjustments.website_id = 1
因为adjustment.website_id
始终为null
,所以不返回任何行。
您可以将此条件移至join
,以得到您期望的行为:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句