SQL“连接”关于空值

Question

由于我无法控制的原因，我需要连接两个表，并且需要空值来匹配。我能想到的最好的选择是吐出一个 UUID 并将其用作我的比较值，但这看起来很难看

SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') = 
   nvl(T2.SOMECOL,'f44087d5935dccbda23f71f3e9beb491')

我怎样才能做得更好？如果重要的话，这在 Oracle 上，并且上下文是一个应用程序，其中必须将一批用户上传的数据与一批现有数据进行比较，以查看是否有任何行匹配。回想起来，我们应该阻止任一数据集中的任何连接列包含空值，但我们没有，现在我们必须忍受它。

编辑：需要明确的是，我不仅仅关心空值。如果列不为空，我希望它们与其实际值匹配。

Answer 1

也许这会起作用，但我从未真正尝试过：

SELECT * 
FROM T1 JOIN T2 
ON T1.SOMECOL = T2.SOMECOL OR (T1.SOMECOL IS NULL AND T2.SOMECOL IS NULL)

Answer 2

在 SQL Server 中，我使用过：

WHERE (a.col = b.col OR COALESCE(a.col, b.col) IS NULL)

显然效率不高，因为 OR，但除非有保留值，否则您可以将 NULL 映射到两侧，而不会产生歧义或折叠，这大约是您能做的最好的事情（如果有，为什么是 NULL甚至允许在您的设计中......）

Answer 3

在 Oracle 中，您可以使用 decode 连接 null 值：

    SELECT * FROM T1 JOIN T2 ON DECODE(T1.SOMECOL, T2.SOMECOL, 1, 0) = 1

decode 将 null 视为相等，因此无需“魔法”数字即可工作。两列必须具有相同的数据类型。

它不会生成最具可读性的代码，但可能仍然比 t1.id = t2.id 或（t1.id 为 null，t2.id 为 null）更好

Answer 4

对于此类任务，Oracle 内部使用未记录的函数 sys_op_map_nonnull()，其中您的查询将变为：

SELECT *
FROM T1 JOIN T2 ON sys_op_map_nonnull(T1.SOMECOL) = sys_op_map_nonnull(T2.SOMECOL)

未记录，因此如果您采用此方法，请务必小心。

Answer 5

您不能做得更好，但是您拥有的 JOIN 不会以任何方式执行实际的“JOIN”（T1.SOMECOL 和 T2.SOMECOL 之间不会有任何关联，除了它们都有一个 NULL 值）柱子）。基本上，这意味着您将无法在 NULL 上使用 JOIN 来查看行是否匹配。

NULL 永远不会等于另一个 NULL。未知价值的东西怎么能等于其他未知价值的东西呢？

Answer 6

很简单，利用COALESCE，它将返回其第一个非空参数：

SELECT * FROM T1 JOIN T2 ON 
  COALESCE(T1.Field, 'magic string') = 
     COALESCE(T2.Field, 'magic string')

您唯一需要担心的是“魔术字符串”不能是任一表中连接字段的合法值。

Answer 7

您可以尝试使用以下查询。

SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);

Answer 8

如果值为空，您真的希望能够连接表吗？难道不能排除连接谓词中可能的空值吗？我发现很难理解两个表中的行可以通过空值关联。如果 table1.col_a 中有 100 个 null，table2.col_b 中有 100 个 null，则仅针对包含 null 的行将返回 10000 行。听起来不正确。

但是，您确实说过您需要它。我是否可以建议将空列合并为较小的字符串，因为字符比较相对昂贵。更好的是，如果列中的数据是文本，则将空值合并为整数。然后您就可以非常快速地进行“比较”，并且不太可能与现有数据发生冲突。

Answer 9

只是把它扔在那里——有没有一种方法可以将这些空值合并成一个已知值，比如空字符串？不太了解你的桌子是如何布局的意味着我不能确定你是否会失去这种方式的意义——即有一个空字符串代表“用户拒绝输入电话号码”而 NULL 代表“我们忘记了”来询问一下”，或者类似的事情？

我确信这是不可能的，但如果是的话，您将有已知的值进行比较，并且可以通过这种方式获得合法的连接。

Answer 10

这不是与检查两列中是否存在空值相同吗？

SELECT * FROM T1, T2 WHERE T1.SOMECOL IS NULL and T2.SOMECOL IS NULL

或者

SELECT * FROM T1 CROSS JOIN T2 WHERE T1.SOMECOL IS NULL and T2.SOMECOL IS NULL

Answer 11

为什么不这样做：

SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'null') =
nvl(T2.SOMECOL,'null')

我不知道你为什么使用 UUID。您可以使用列中不存在的任何字符串，例如字符串“null”，以减少内存占用。例如，使用 nvl 的解决方案比 Eric Petroelje 提出的使用 or ... is null 的解决方案要快得多。

Answer 12

我相信您仍然可以使用 nvl() 进行连接：

SELECT *
FROM T1
JOIN T2 ON NVL(T2.COL1,-1)=NVL(T1.COL1,-1);

但是您需要在列 col1 上添加基于函数的索引 索引

CREATE INDEX IND_1 ON T1 (NVL(COL1,-1));
CREATE INDEX IND_2 ON T2 (NVL(COL1,-1));

应该会显着提高 NVL(..) 上的连接速度。

Answer 13

@Sarath Avanavu

这不是最好的方法。如果 TA.COL1 保留值 0 并且 TB.COL2 为 NULL，它将连接这些记录，这是不正确的。

SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);

Answer 14

您还可以使用 CASE 替换子查询中的 null 值，然后 JOIN 结果：

SELECT T1.COL1 FROM
(
   (SELECT (CASE WHEN COL1 IS NULL THEN 'X' ELSE COL1 END) AS COL1 FROM TABLE1) T1
   JOIN
   (SELECT (CASE WHEN COL1 IS NULL THEN 'X' ELSE COL1 END) AS COL1 FROM TABLE2) T2
)
ON T1.COL1=T2.COL1