根據A中的屬性從A左連接B中獲取所有行

我有 2 個表products和stores，中間有一個名為 的數據透視表product_store。

這是表結構的相關部分（來自 MySQL）：

產品

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

商店

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

產品商店

`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,

globalvs標誌的邏輯show如下：

• 如果global標誌為 1，則假設該產品在每個商店中，除非有== 0的product_store條目。show
• 如果global標誌為 0，則假設該產品僅在有== 1product_store條目的商店中銷售。show

也就是說，global== 1 表示在任何地方都展示該產品，show== 1 表示在本店展示該產品，show== 0 表示不在本店展示該產品，並show優先於global。

我想要做的是根據已知的 store_id 獲取產品，其中：

• products.global == 1 並且 product_id 和 store_id 沒有 product_store 條目
• 要麼
• product_id 有一個 product_store 條目，store_id AND product_store.show == 1

目前我有這個查詢，帶有 aGROUP BY和 a 嵌套SELECT，但它從數據庫中提取了許多記錄，然後由 the 合併，GROUP BY而且速度很慢。我認為必須有更快的東西，沒有嵌套選擇。

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) = 0)
        )
       OR (product_store.store_id=226 AND product_store.show=1)
       )
   GROUP BY products.id
   ;

226是本例中的商店 ID，我一次只需要為一個商店執行此查詢，因此可以將其視為常量。

請注意，如果我使用 進行上述查詢，GROUP BY那麼我會得到大約 14 條正確的記錄（當我自己進行計數時）。系統有幾千個產品和幾百個商店，因此product_store連接表相當大。如果我在沒有 的情況下執行上述查詢，GROUP BY那麼我將獲得 ~ 1500 條記錄，這些記錄似乎是符合該LEFT JOIN子句的所有產品/產品商店組合的列表。

只是為了重新迭代，上面的查詢工作正常（儘管在 MySQL 版本 > 5.7.5 中會有問題GROUP BY），但它似乎效率低下，我正試圖找出更好更快的東西。

有關表格內容的更多詳細資訊

這裡有一些關於相關產品和商店等的快速查詢。首先顯示所有相關匹配的產品——共有 15 個，所有這些產品的 global=1。

SELECT products.id, MD5(products.name), products.global
   FROM products
   -- .. omitted some classification information here
   ;

+-----+----------------------------------+--------+
| id  | md5(products.name)               | global |
+-----+----------------------------------+--------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |
| 591 | 7ba5549546ce20e1dee02c7f6454b16f |      1 |
| 558 | 7b66d8dce0c112b3966d1940de44f9ad |      1 |
| 556 | bd3d39b9f5194184ea0853c70d7faa07 |      1 |
| 590 | ef6eccbd3795fbe70890220c09ed880d |      1 |
| 559 | b763e2b9ba305538b497da97fba066ca |      1 |
| 593 | e26744d617ed12ebffea4b996fecbbef |      1 |
| 594 | 06ba8ab6649c83577db053e4a89c1655 |      1 |
| 596 | 79f9241643e84e2a27bfc0b260432e82 |      1 |
| 595 | 85fe772dc61d27292c09a7604cf76dd7 |      1 |
| 597 | fd6c21db00e1b679c0d0d2ef8f3f6e00 |      1 |
| 560 | c8056818e3ed5885188d78a9440fa77c |      1 |
| 561 | 33d79c194e39e8e84cf743eefcd909df |      1 |
| 562 | 1c74d85823e509ab4b09e82314b09604 |      1 |
| 557 | b6a8027035b63bd4d4bd21169434426d |      1 |
+-----+----------------------------------+--------+

現在查看與 product_store 表的連接。store_id 226 只有一個條目，它的 show=0。

SELECT products.id, MD5(products.name), products.global, product_store.show
   FROM products
   INNER JOIN product_store ON products.id=product_store.product_id
   -- omitted the classification information as per the above
   WHERE product_store.store_id=226
   ;

+-----+----------------------------------+--------+------+
| id  | MD5(products.name)               | global | show |
+-----+----------------------------------+--------+------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |    0 |
+-----+----------------------------------+--------+------+

…因此，我需要能夠建構一個查詢，該查詢選擇 global=1 的所有 15 種產品，除了 product_store.show=0 的產品，因此我需要剩餘 14 種產品的集合。

UNION 選擇會以某種方式工作嗎？還是某種形式的相交？

範例：http ://sqlfiddle.com/#!9/1342cf/5

根據您的範例，這似乎是一個等效查詢：

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不應該是必要的。

我將此添加到您的小提琴中

ON在vs.中放置條件WHERE對於外部連接至關重要。這ON只是一個從不過濾行但可能在內部表中創建 NULL 的條件。在聯接之後WHERE應用，在這裡它過濾（當應用於內部表的列時，外部聯接創建的所有 NULL 都將被再次刪除，除非您添加）。OR col IS NULL

引用自：https://dba.stackexchange.com/questions/181502