mysql - SELECT，从 JOIN 上的 WHERE 获取反转结果

Question

我有这个 MySQL 请求让所有商店在特定日期有发票：

SELECT sh__shop.id AS shop_id, sh__shop.name,
       sh__shop_invoice.*
FROM sh__shop 
LEFT JOIN sh__shop_invoice ON sh__shop_invoice.shop_id = sh__shop.id
WHERE sh__shop_invoice.month_commission LIKE "2021-08-01"

但我看不出如何得到逆。所有在特定日期没有发票的商店。我做不到NOT LIKE "2021-08-01"。

标签： mysqlsqljoinwhere-clauseinverse

Answer 1

您可以使用not exists.

SELECT sh__shop.id AS shop_id, sh__shop.name
FROM sh__shop 
WHERE 
    NOT EXISTS (SELECT * FROM sh__shop_invoice i
            WHERE i.shop_id = sh__shop.id
                  AND i.month_commission LIKE "2021-08-01");