SQL LEFT 外连接仅包含右侧的一些行？

Question

我有两个表 TABLE_A 和 TABLE_B，其连接列作为员工编号 EMPNO。

我想做一个正常的左外连接。但是，TABLE_B 具有某些被软删除的记录 (status='D')，我希望将这些记录包括在内。只是为了澄清，TABLE_B 可能有活动记录（状态 = null/a/anything）以及已删除的记录，在这种情况下，我不希望该员工出现在我的结果中。但是，如果 TABLE_B 中只有已删除的员工记录，我希望该员工包含在结果中。我希望我能明确我的要求。 （我可以做一个冗长的 qrslt 之类的事情并得到我想要的东西，但我认为必须有一种更优化的方法来使用连接语法来做到这一点）。将不胜感激任何建议（即使没有加入）。他的新手正在尝试以下查询，但没有得到所需的结果：

SELECT TABLE_A.EMPNO
FROM   TABLE_A
LEFT OUTER JOIN TABLE_B ON TABLE_A.EMPNO = TABLE_B.EMPNO AND TABLE_B.STATUS<>'D' 

非常感谢任何帮助。

Answer 1

只是为了澄清 - TABLE_A 中的所有记录都应该出现，除非表 B 中的行的雕像除了而不是“D”？

您将需要 B 上至少一个非空列（我将使用“B.ID”作为示例，这种方法应该有效）：

SELECT TABLE_A.EMPNO
FROM TABLE_A
LEFT OUTER JOIN TABLE_B ON
  (TABLE_A.EMPNO = TABLE_B.EMPNO)
  AND (TABLE_B.STATUS <> 'D' OR TABLE_B.STATUS IS NULL)
WHERE
  TABLE_B.ID IS NULL

也就是说，反转您可能想到的逻辑 - 仅在以下位置加入到 TABLE_B：您有一些行排除 TABLE_A 条目，然后在末尾使用 IS NULL 来排除这些行。这意味着仅包含那些不匹配的内容（TABLE_B 中没有行或仅包含“D”行的内容）。

另一种选择可能是

SELECT TABLE_A.EMPNO
FROM TABLE_A
WHERE NOT EXISTS (
  SELECT * FROM TABLE_B 
  WHERE TABLE_B.EMPNO = TABLE_A.EMPNO
  AND (TABLE_B.STATUS <> 'D' OR TABLE_B.STATUS IS NULL)
)

Answer 2

以下查询将获取未删除的员工记录，或仅该员工已删除的记录。

select
    a.*
from
    table_a a
    left join table_b b on
        a.empno = b.empno
where
    b.status <> 'D'
    or (b.status = 'D' and 
        (select count(distinct status) from table_b where empno = a.empno) = 1)

这是用 ANSI SQL 编写的，但如果我知道你的 RDBMS，我可以给出一个更具体的解决方案，可能会更优雅一些。

Answer 3

啊粗鲁，这显然有效>><

SELECT TABLE_A.EMPNO
FROM   TABLE_A
LEFT OUTER JOIN TABLE_B ON TABLE_A.EMPNO = TABLE_B.EMPNO 
where TABLE_B.STATUS<>'D'

如果你们有任何额外的信息可以补充，请随意。

更新：
一段时间后看到这个问题，我想我会添加更多有用的信息：此链接有关于 ANSI 语法的良好信息 - http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php

特别是链接页面中的这一部分内容丰富：

可以将额外的过滤条件添加到连接中，以使用 AND 形成复杂的连接。当需要过滤条件来限制外部联接时，这些通常是必要的。如果这些过滤条件放置在 WHERE 子句中，并且外连接为过滤列返回 NULL 值，则该行将被丢弃。如果将过滤条件编码为连接的一部分，则可以避免这种情况。

Answer 4

SELECT A.*, B.*
FROM
   Table_A A
   INNER JOIN Table_B B
      ON A.EmpNo = B.EmpNo
WHERE
   NOT EXISTS (
      SELECT *
      FROM Table_B X
      WHERE
          A.EmpNo = X.EmpNo
          AND X.Status <> 'D'
    )

我认为这可以解决问题。不需要左联接，因为您只想包含所有（和至少一个）已删除行的员工。

Answer 5

我是这样理解这个问题的。您只需包含满足以下任一条件的员工：

• 员工在 TABLE_B 中仅具有（软）删除的行；

• 员工在 TABLE_B 中仅拥有未删除的行；

• 员工在 TABLE_B 中根本没有行。

换句话说，如果某个员工在 TABLE_B 中同时拥有已删除和未删除的行，则忽略该员工，否则将其包括在内。

这就是我认为可以解决的方法：

SELECT DISTINCT a.EMPNO
FROM TABLE_A a
  LEFT JOIN TABLE_B b1 ON a.EMPNO = b1.EMPNO
  LEFT JOIN TABLE_B b2 ON b1.EMPNO = b2.EMPNO
    AND (b1.STATUS = 'D' AND (b2.STATUS <> 'D' OR b2 IS NULL) OR
         b2.STATUS = 'D' AND (b1.STATUS <> 'D' OR b1 IS NULL))
WHERE b2.EMPNO /* or whatever non-nullable column there is */ IS NULL

不过，或者，您可以使用分组：

SELECT a.EMPNO
FROM TABLE_A a
  LEFT JOIN TABLE_B b ON a.EMPNO = b1.EMPNO
GROUP BY a.EMPNO
HAVING 0 IN (COUNT(CASE b.STATUS WHEN 'D' THEN 1 ELSE NULL END),
             COUNT(CASE b.STATUS WHEN 'D' THEN NULL ELSE 1 END))