mysql - 从两个 mysql 表中查找缺失的列组合

Question

我有一个这样的产品表:

productid    categoryid

还有另一个像这样的类别表:

categoryid   parentid

想要找到产品表中缺失的行

select distinct c.parentid, pc.productid, from products_categories pc
join categories c on pc.categoryid = c.categoryid
where  concat(pc.productid,'-',c.parentid) not in (
 select distinct concat(productid,'-',categoryid) from products_categories
)

但是这非常慢。有没有办法用 joins 而不是 not in concat 行来做到这一点？ concat 用于说明所有可能的组合。

Answer 1

每次您将前缀 productID 和 - 添加到 parentID 或 categoryID 时。

你可以试试这个:

SELECT DISTINCT c.parentid, pc.productid 
FROM products_categories pc
  JOIN categories c on pc.categoryid = c.categoryid
WHERE c.parentid NOT IN (
  SELECT DISTINCT categoryid FROM products_categories
)

Edit 1: doesnt account for all combos

SELECT DISTINCT c1.parentid, pc.productid 
FROM products_categories pc
  INNER JOIN categories c1 ON pc.categoryid = c1.categoryid
  LEFT JOIN categories c2 ON c1.parentid = c2.categoryid
WHERE c2.categoryid IS NULL