SQL 其中 ID 等于 ID 但可以为空 [英] SQL Where ID equals ID but can be null

问题描述

我正在尝试从数据库中获取一些数据.我有一个 Taak 表，其中包含一个可能的 idPartij 列.可能，因为它可以是真正的 idPartij，也可以是 null.

I'm trying to fetch some data from an db. I've got an Taak table with an possible idPartij column. Possible, because it can be an real idPartij, but can also be null.

我得到的查询:

SELECT T.idTaak,
       T.Taaktype,
       P.Partijnaam,
       T.Naar,
       T.UltimatumDatum,
       T.Opmerking,
       T.Status,
       T.Prioriteit
FROM Taak AS T,
     Partij AS P
WHERE T.idPartij = P.idPartij
ORDER BY idTaak DESC

当我在 T.idPartij 中有一个 id 时，这工作正常，但如前所述，该 id 可以是 null.如果是这种情况，该行将不会出现在结果中.

This is working fine when I've got an id in T.idPartij, but as mentioned earlier, that id can be null. And when that is the case, the row won't be in the result.

唯一的问题:当我删除 where 子句时，我得到了很多行(因为 Partij 表不再被过滤......

Only problem: When I remove the where clause, I get the rows a lot ( because the Partij table isn't filtered anymore...

我对 SQL 完全是个菜鸟，所以我想不出解决"这个问题的方法.我唯一能想到的是创建 2 个查询，但我认为这不是一个好方法...

I'm an total noob at SQL, so i can't think of an way to "fix" this problem. Only thing i can think of is creating 2 query's, but i don't think that is an nice way to do...

推荐答案

由于您正在尝试链接两个表，因此您应该使用 LEFT OUTER JOIN 代替:

Since you are trying to link two tables, you should use a LEFT OUTER JOIN instead:

SELECT T.idTaak,
       T.Taaktype,
       P.Partijnaam,
       T.Naar,
       T.UltimatumDatum,
       T.Opmerking,
       T.Status,
       T.Prioriteit
FROM Taak AS T LEFT OUTER JOIN Partij AS P
   ON T.idPartij = P.idPartij
ORDER BY idTaak DESC

如果您不想包含 NULL 值，请改用 INNER JOIN.

If you don't want to include the NULL values use an INNER JOIN instead.

请注意，您不应使用这些旧式连接 踢球是个坏习惯.

Note that you should not use these old-style-joins it's a bad habit to kick.

根据NULL问题:

您不能使用 = NULL 或 <>NULL 因为 NULL 不等于或不等于任何东西.NULL 表示未知.

You cannot use = NULL or <> NULL because NULL is not equal or unequal to anything. NULL means unknown.

所以使用 IS NULL 或 IS NOT NULL:

WHERE T.idPartij IS NULL OR T.idPartij = P.idPartij

来自 MSDN

NULL 值表示该值未知.NULL 值不同于空值或零值.没有两个空值是平等的.两个空值之间的比较，或一个 NULL 和任何值之间的比较其他值，返回未知，因为每个 NULL 的值都是未知的.

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

这篇关于SQL 其中 ID 等于 ID 但可以为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！