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

when I select table like this: select count(*) from products is returning 12900 result.

当我这样选择表时:从产品中选择count(*)将返回12900结果。

I have a relational query that returns multiple table relation reult like this:

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

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

This query returns 12873 result,

该查询返回12873结果，

So I can not find which data is not matched. How can I find this lost data? I used NOT query but did not return any result.

所以我找不到不匹配的数据。如何找到丢失的数据?我使用了NOT query，但是没有返回任何结果。

4 个解决方案

#1

You can see that not matched records with FULL OUTER JOIN like this:

您可以看到没有匹配的记录与完整的外部联接如下:

SELECT prod.id, rel.id FROM (
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
) as rel
FULL OUTER JOIN products as prod
ON rel.id = prod.id

So you can see null id and not null ids in list.

你可以在列表中看到空id和非空id。

#2

First, you should learn to use proper, explicit join syntax:

首先，您应该学会使用适当的、显式的连接语法:

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;

Then if you want non-matches, switch to left join and look for non-matches in the where clause:

然后，如果您想要非匹配，请切换到左连接，并在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;

#3

In addition to my comments about aliasing above you should use the "newer" join syntax introduced in ANSI-92. Notice how much less code there is here for the same thing. The way you wrote your code all your joins were inner joins, since you want to return rows with no match I changed them to left joins.

除了我对上述混叠的评论之外，您还应该使用ANSI-92中引入的“更新”连接语法。注意，对于相同的东西，这里的代码要少得多。您编写代码的方式是所有的连接都是内部连接，因为您希望返回没有匹配的行，所以我将它们更改为左连接。

SELECT c.name, 
       m.name,
       s.name,
       p.name
FROM products p
left join suppliers s on p.sid = s.id
left join manifacturers m on p.manid = m.id
left join categories c on p.catid = c.id

#4

-1

Below Query returns those records whose products are not present in your Query :

以下查询返回查询中未显示产品的记录:

SELECT *
FROM Products P
WHERE (P.sid,P.mainid,P.catid) NOT IN (
    SELECT DISTINCT product.sid
    ,product.mainid
    ,product.catid
    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
    )

#1