仅自连接并忽略一个表/端上的空值 - self join and ignore nulls on one table / side only

我有2张桌子。

一个表定义客户连接：

    CREATE TABLE IF NOT EXISTS `cust_connections` (
    `id` int(11) NOT NULL,
      `short_name` char(15) COLLATE utf8_unicode_ci NOT NULL,
      `source_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `dest_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `service_type` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `ladder_side` char(10) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    INSERT INTO `cust_connections` (`id`, `short_name`, `source_fnn`, `dest_fnn`, `service_type`, `ladder_side`) VALUES
    (1, 'cust1', 'N2843453A', '', 'HD_300_Connect', 'src only'),
    (2, 'cust2', '', 'N2843600A', 'HD_300_Connect', 'dest only'),
    (3, 'cust3', 'N2720257O', 'N2731164O', 'DVB25_188byte', 'both'),
    (4, 'cust4', 'N27xxx7O', 'N2731164O', 'DVB25_188byte', 'src ukn'),
    (5, 'cust4', 'N27xxx7O', '', 'DVB25_188byte', 'ukn +blk');
ALTER TABLE `cust_connections`
 ADD PRIMARY KEY (`id`);
ALTER TABLE `cust_connections`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;

另一个表定义了设备：

     CREATE TABLE IF NOT EXISTS `cust_port` (
    `id` smallint(11) NOT NULL,
      `system_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `slot_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_fnn` char(9) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    INSERT INTO `cust_port` (`id`, `system_name`, `slot_no`, `port_no`, `port_fnn`) VALUES
    (1, '01-06C2:source', '7', '1', 'N2843453A'),
    (2, '01-27B4:dest', '1', '2', 'N2843600A'),
    (3, '01-27B6:source+dst', '17', '3', 'N2720257O'),
    (4, '01-27B6:dst+src', '17', '3', 'N2731164O'),
    (5, '01-32C6:dup_fnn1', '1', '2', 'N2845070O'),
    (26, '01-32C6:dup_fnn2', '1', '3', 'N2845070O'),
    (27, '01-32D6:no_fnn', '1', '4', ''),
    (28, '01-32D6:diff_fnn', '1', '4', 'x123456');
ALTER TABLE `cust_port`
 ADD PRIMARY KEY (`id`);
ALTER TABLE `cust_port`
MODIFY `id` smallint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=29;

SQl 结果为：

 cc_id  short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       01-32D6:no_fnn       1              4   
2       cust2       N2843600A               HD_300_Connect  dest only      01-32D6:no_fnn         1                  4                          01-27B4:dest         1              2               N2843600A
3       cust3       N2720257O   N2731164O   DVB25_188byte   both           01-27B6:source+dst    17                  3          N2720257O       01-27B6:dst+src      17             3               N2731164O
4       cust4       N27xxx7O    N2731164O   DVB25_188byte   src ukn        NULL                   NULL               NULL       NULL            01-27B6:dst+src      17             3               N2731164O
5       cust4       N27xxx7O                DVB25_188byte   ukn +blk       NULL                   NULL               NULL       NULL            01-32D6:no_fnn       1              4

我正在两张桌子上做一个连接。

问题是，如果port_fnn为空，我想排除该行，但如果任何客户源或目标 fnn 为空，则显示该行。

我正在执行左（自我）连接以将源和目标 fnn 与设备 fnn 进行匹配。不幸的是，我的客户 fnns 必须能够具有空值。

如果设备表中没有空值，我的查询效果很好。我的查询是：

SELECT 
cc.id AS cc_id, short_name,source_fnn, dest_fnn, service_type,ladder_side,
src.system_name AS src_system_name,
src.slot_no AS src_slot_no,
src.port_no AS src_port_no,
src.port_fnn AS src_port_fnn,
dst.system_name AS dst_system_name,
dst.slot_no AS dst_slot_no, 
dst.port_no AS dst_port_no,
dst.port_fnn AS dst_port_fnn
FROM cust_connections cc 
 LEFT JOIN cust_port src on cc.source_fnn=src.port_fnn 
 LEFT JOIN cust_port dst on cc.dest_fnn=dst.port_fnn

在我的结果集中：第 1 行 - 仅具有源 fnn。我希望目标字段的结果为空，即：

cc_id   short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       NULL                NULL             NULL            NULL

查询正在检测空 fnn，并使用未关联的 fnn 的设备进行填充。即：01-32D6：no_fnn。

第 2 行的source_system_name和第 5 行的dst_system_name也会出现相同的问题。

''（空字符串）不是 NULL。（哪个sqlfiddle输出为"（null）"。

在

文本中，不要像您最初在问题中那样写"NULL"或"或"null"或"（null）"来引用空字符串。明确什么是"，什么是空。

'' = '' 但 NULL <> NULL。因此，当相等性测试涉及 '' 时，您的 LEFT JOIN 会查找列 cc.source_fnn 和 src.port_fnn 列之间以及列 cc.dest_fnn 和cust_port port_fnn之间的匹配项。但是您不希望 LEFT JOIN 与这些行匹配。

你可以这样说：

声明所有_fnn列可为空，即为 NULL（默认值）而不是 NOT NULL，并在您现在使用 ''（空字符串）的表中使用 NULL。然后您的查询将给出正确的答案！

要求port_fnn <> ''：

FROM cust_connections cc
LEFT JOIN cust_port src
ON cc.source_fnn=src.port_fnn AND cc.source_fnn <> ''
LEFT JOIN cust_port  dst
ON cc.dest_fnn=dst.port_fnn AND cc.dest_fnn <> '';

在你离开加入之前从cust_port中删除这些行：

FROM cust_connections cc
LEFT JOIN
    (SELECT * FROM cust_port WHERE port_fnn <> ''
    ) src
ON cc.source_fnn=src.port_fnn
LEFT JOIN
    (SELECT * FROM cust_port WHERE port_fnn <> ''
    ) dst
ON cc.dest_fnn=dst.port_fnn;

Sqlfiddle for 1 使用 NULL 和 2 和 3 使用 ''。它们添加了第二行cust_port没有port_fnn，以表明上面给出了正确的结果。与 '' 一起使用的查询错误地从中生成其他虚假行。

如果您希望 LEFT JOIN 中的 NULL 在输出时显示为空字符串，则可以对这些列使用 IFNULL：

IFNULL（dst.port_fnn，''） AS dst_port_fnn

您可以添加如下where子句：

where  (cc.source_fnn is null or cc.dest_fnn is null)
       or (src.port_fnn is not null or dst.port_fnn is not null)

现在，它将始终显示具有空source_fnn或dest_fnn的行。当两者都填充时，它将过滤掉匹配port_fnn列为空的行。

因此，您将获得缺少外键的行

，但禁止显示外键引用具有空列的行的行。至少我认为这是你要找的。如果不是，请澄清您的问题。

我认为您正在尝试做的事情可以使用每个源/目标列的 IF（）来完成，以掩盖名称和端口。大多数时候，人们会尝试做一些事情来防止空值并显示空字符串之类的东西......相反，你想要相反...如果"port_fnn"为空，则要隐藏这些元素。

所以我为每列做了一个 IF（表达式，结果如果真，结果如果假）。因此，如果port_fnn为 NULL，则显示 null 作为结果，否则返回列中的任何内容（系统名称、插槽、端口等）

SELECT 
      cc.id AS cc_id, 
      short_name,
      source_fnn, 
      dest_fnn, 
      service_type,ladder_side,
      if( src.port_fnn = '', NULL, src.system_name ) AS src_system_name,
      if( src.port_fnn = '', NULL, src.slot_no ) AS src_slot_no,
      if( src.port_fnn = '', NULL, src.port_no ) AS src_port_no,
      if( src.port_fnn = '', NULL, src.port_fnn ) AS src_port_fnn,
      if( dst.port_fnn = '', NULL, dst.system_name ) AS dst_system_name,
      if( dst.port_fnn = '', NULL, dst.slot_no ) AS dst_slot_no, 
      if( dst.port_fnn = '', NULL, dst.port_no ) AS dst_port_no,
      if( dst.port_fnn = '', NULL, dst.port_fnn ) AS dst_port_fnn
   FROM 
      cust_connections cc 
          LEFT JOIN cust_port src 
             on cc.source_fnn = src.port_fnn
          LEFT JOIN cust_port dst 
             on cc.dest_fnn = dst.port_fnn

我调整了上面的查询以处理您的数据...NULL 不同于空字符串。我将您的表和示例数据复制到 SQL Fiddle，然后是上面的查询。它似乎分别显示源和目标的 NULL。

SQLFiddle 根据您的示例结构和数据

[这个答案是在早期版本的 qeustion 的上下文中，其中示例输入不清楚地将空字符串即 '' 描述为 NULL//null，因此看起来 " 值是 NULL。如果他们是空的，这个答案适用。Sqlfiddle]

错误的"期望输出"

"所需输出"中的 equip4 1/2 是一个错误，您希望从端口 ID 1 1 通过 4444 配备 1/2。更正后，所需的输出就是您在其他地方描述的（不清楚）。

空port_fnn不是问题

空port_fnn不会将查询中的任何内容放入表中。左联接永远不会匹配它。

您想要什么行？

你不清楚。唯一不寻址的行是非 NULL source_fnn或 dest_fnn 没有匹配port_fn。然后在左联接输出中，它与 NULL port_fnn 信息配对。

如果你不想要这些行，那就太奇怪了。您不仅不会为cust_connection行，而且扩展信息的另一半也会随之丢弃。也许您只会丢弃源和 dest 都不是 NULL 且匹配的行。还是很奇怪。您必须告诉我们是否需要这些行。大概你想要它们，因为它们在你的查询中。

如果每个source_fnn和dest_fnn都有一个匹配的port_fnn，即有从source_fnn和dest_fnn到port_fnn的外键，那么这永远不会发生，你的查询是正确的。

所以你的查询似乎没问题。

而且您似乎错误地猜测了 NULL port_fnn无法解释为什么它与您错误的预期输出不同。