mysql - 从一个表中选择不在其他表中的记录

Question

我的数据库中有两个表:

表 1(BonInterne):

+---------+
|   num   | 
+---------+
|1        |
+---------+
|2        |
+---------+
|3        |
+---------+
|4        |
+---------+

表 2(LigneBonInterne):

+---------+-----------+
|numOrdre |numBon     |
+---------+-----------+
|20       |1          +
+---------+-----------+
|21       |2          |
+---------+-----------+
|22       |NULL       +
+---------+-----------+
|23       |2          |
+---------+-----------+
|24       |1          +
+---------+-----------+
|25       |2          |
+---------+-----------+
|26       |NULL       +
+---------+-----------+
|27       |1          |
+---------+-----------+

我想从表 1 中获取所有记录，其中有一个数字不在第二个表中，这是我试过的脚本:

SELECT * FROM BonInterne WHERE num NOT IN (SELECT numBon FROM LigneBonInterne)

但是 MySQL 返回了一个空结果集。

他应该返回如下:

+---------+
|   num   | 
+---------+
|3        |
+---------+
|4        |
+---------+

我必须在查询中更改什么才能获取该表？？

Answer 1

Select B.num
From BonInterne As B
    Left Join LigneBonInterne As L
        On L.numBon = B.num
Where L.numBon Is Null  

SQL Fiddle version

Visual Explanation of Joins

为什么您的原始查询不起作用的一个解释是 LigneBonInterne表包含 numBon 的空值柱子。 In函数转化为一系列 Or语句(例如 Foo In(A,B,C) 等于 Foo = A Or Foo = B Or Foo = C 。在 Foo Not In(A,B,C) 的情况下，我们得到 Foo <> A And Foo <> B And Foo <> C 。)。因此，如果其中一个值是 null，我们将得到与 null 的比较，这将返回 false(技术上它返回 Unknown)。

这SQL Fiddle example显示您的原始查询，但请注意我为 numBon 添加了一个空值并且没有得到结果。去掉这个值，我们就这样做了。

如果您确实在 LigneBonInterne 中有空值，那么上述解决方案将起作用。另一种选择是使用 In函数但过滤掉子查询中的空值

Select B.num
From BonInterne As B
Where Not In    (
                Select L1.numBon
                From LigneBonInterne As L1
                Where L1.numBon Is Not Null
                )

另一种选择是使用 Exists而不是 In :

Select B.num
From BonInterne As B
Where Not Exists    (
                    Select 1
                    From LigneBonInterne As L1
                    Where L1.numBon = B.num
                    )

使用 Exists 时, Select条款被完全忽略。有些人使用 Select * , 有些人使用 Select Null , 一些使用 Select 1 .没关系；重要的是其余查询是否返回行。这种类型的查询称为相关子查询，因为在内部查询中存在对外部查询列的引用 ( L1.numBon = B.num )。

那么，您应该使用哪种形式？在这种情况下，可以通过三种形式中的任何一种来明确意图。然而，数据库产品在有效处理相关子查询的能力方面各不相同。就 MySQL 而言，它可能会在使用 Left Join 时表现最佳，然后是 In 函数，最后是 Exists 函数。