Fetch all rows from A left join B based on attribute in A

I have 2 tables, products and stores, and a pivot table in the middle called product_store.

Here is the relevant portion of the table structures (from MySQL):

products

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`global` tinyint(1) NOT NULL DEFAULT '0',

stores

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,

product_store

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`store_id` int(10) unsigned NOT NULL,
`show` tinyint(1) NOT NULL,

The logic of the global vs show flags are as follows:

• If the global flag is 1, then assume this product is on every store, except when there is a product_store entry with show == 0.

• If the global flag is 0, then assume this product is only on stores where there is a product_store entry with show == 1.

In other words, global == 1 means show this product everywhere, show == 1 means show the product in this store, show == 0 means do not show the product in this store, and show takes precedence over global.

What I want to be able to do is fetch products based on a known store_id, where:

• products.global == 1 AND there is NO product_store entry for the product_id, store_id

• OR

• there is a product_store entry for the product_id, store_id AND product_store.show == 1

Currently I have this query, with a GROUP BY and a nested SELECT but it pulls many records from the database which are then consolidated by the GROUP BY and it's quite slow. I am thinking that there must be something that's faster, without the nested select.

```
SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
FROM products
LEFT JOIN product_store ON products.id=product_store.product_id
WHERE ((products.global=1 AND
((SELECT COUNT(*) FROM product_store WHERE product_store.store_id=226 AND product_store.product_id=products.id) =

Based on your sample, this seems to be an equivalent query:

SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
FROM products
LEFT JOIN product_store
  ON products.id=product_store.product_id
 AND product_store.store_id=226
WHERE -- there is NO entry in product_store AND products.global=1
      (products.global=1 AND product_store.show IS NULL)
      -- there is an entry in product_store with show=1 
   OR (products.global=0 AND product_store.show = 1)

DISTINCT should not be neccessary.

I added this to your fiddle

Placing conditions in ON vs. WHERE is crucial for outer joins. In ON it's just a condition which never filters rows but might create NULLs in the inner table. WHERE is applied after the join, and here it filters (when applied on a column of the inner table all NULLs created by the outer join will be removed again, unless you add OR col IS NULL).

StackExchange Database Administrators Q#181502, answer score: 2