Oracle SQL Self-Join:如何包含缺少的记录

Question

我想要显示名称和计划，即使它们不是记录。我尝试在这个问题上使用self join，但没有成功:(

脚本：

    SELECT DISTINCT    A.NAME  , B.PLAN
    FROM PLAYER A  
    LEFT JOIN PLAYER B
    ON A.NAME = B.NAME 
    WHERE A.NAME IN ( 'BOGGIE', 'STEPH')
    AND  B.PLAN IN ('PLAN200' , 'PLAN999');

当前结果

    NAME    PLAN
    BOOGIE  PLAN200
    STEPH   PLAN200

预期结果

    NAME    PLAN
    BOOGIE  PLAN200
    BOOGIE  
    STEPH   PLAN200
    STEPH   

表格

    NAME    PLAN    AMOUNT  CONTRACT
    BOGGIE  PLAN200   200      24
    STEPH   PLAN200   200      24

Answer 1

您可以尝试在ON clause中使用put您的其他条件

SELECT DISTINCT    A.NAME  , B.PLAN
    FROM PLAYER A  
    LEFT JOIN PLAYER B
    ON A.NAME = B.NAME 
    and A.NAME IN ( 'BOGGIE', 'STEPH')
    AND  B.PLAN IN ('PLAN200' , 'PLAN999');

Answer 2

查询的问题是：

A.NAME IN ( 'BOGGIE', 'STEPH') AND B.PLAN IN ('PLAN200' , 'PLAN999');

查询将left join放在NAME上，但在应用left join之后，将进一步过滤记录。应该在left join子句中正确添加where子句条件。

下面的查询应该给出预期的结果。

SELECT DISTINCT    A.NAME  , B.PLAN
FROM PLAYER A 
LEFT JOIN PLAYER B
ON A.NAME = B.NAME
AND (A.NAME IN ( 'BOGGIE', 'STEPH')
OR B.PLAN IN ('PLAN200' , 'PLAN999'));

Answer 3

问题中的样本数据不完整，无法获得所需的输出；请尝试以下方法。为BOGGIE和STEPH添加了两行空计划；让我们知道这就足够了。

WITH player
     AS (SELECT 'BOGGIE' name,
                'PLAN200' plan,
                200 amount,
                24 contract
           FROM DUAL
         UNION
         SELECT 'STEPH ' name,
                'PLAN200' plan,
                200 amount,
                24 contract
           FROM DUAL
         UNION
         SELECT 'BOGGIE' name,
                NULL plan,
                NULL amount,
                NULL contract
           FROM DUAL
         UNION
         SELECT 'STEPH ' name,
                NULL plan,
                NULL amount,
                NULL contract
           FROM DUAL)
SELECT DISTINCT a.name, b.plan
  FROM player a
       LEFT OUTER JOIN player b
          ON     a.name = b.name
             AND (   a.name IN ('BOGGIE', 'STEPH')
                  OR b.plan IN ('PLAN200', 'PLAN999')) 

输出

NAME   PLAN   
------ -------
BOGGIE PLAN200
BOGGIE        
STEPH  PLAN200
STEPH