Search code examples
mysqlsqlquery-optimization

Join type based on column value

I have two tables:

shops
id | shop_name | shop_type

products
id | shop_id | product_name

I need to LEFT JOIN products ON products.shop_id = shops.id IF shop_type is 0 and I need to JOIN products ON products.shop_id = shops.id IF shop_type is 1. What's the fastest solution? It does not matter if code will be very dirty, performance is the most important thing there..

Solution 
  • select *
from shops LEFT JOIN products ON products.shop_id = shops.id
where shop_type = 0 or (products.shop_id is not NULL and shop_type = 1)