sql - 如何在关系型 SQL 查询中使用 NOT

Question

当我选择这样的表时:select count(*) from products 返回 12900 结果。

我有一个关系查询，它返回多个表关系结果，如下所示:

SELECT category.name, 
       manifacturer.name,
       supplier.name,
       product.name
FROM   products as product,
       suppliers as supplier, 
       manifacturers as manifacturer,
       categories as category
WHERE  product.sid=supplier.id AND
       product.manid = manifacturer.id AND
       product.catid = category.id

此查询返回 12873 个结果，

所以我找不到哪些数据不匹配。我怎样才能找到这些丢失的数据？我使用NOT查询但没有返回任何结果。

Answer 1

首先，您应该学习使用正确、明确的 join 语法:

SELECT category.name, manifacturer.name, supplier.name, product.name
FROM   products as product join
       suppliers as supplier
       on product.sid = supplier.id join
       manifacturers as manifacturer
       on product.manid = manifacturer.id join
       categories as category
       on product.catid = category.id;

然后，如果您想要非匹配项，请切换到 left join 并在 where 子句中查找非匹配项:

SELECT category.name, manifacturer.name, supplier.name, product.name
FROM   products as product left join
       suppliers as supplier
       on product.sid = supplier.id left join
       manifacturers as manifacturer
       on product.manid = manifacturer.id left join
       categories as category
       on product.catid = category.id
WHERE supplier.id IS NULL OR manifacturer.id IS NULL or category.id IS NULL;