与在子句 Oracle 上的联接没有区别

Question

我有一个用左外部连接结构的查询，如下所示：

left outer JOIN GA_LOAN GA 
  ON LOAN.LOAN_TYPE = GA.LOAN_TYP
    AND LOAN.DT = GA.GUARANTY_DT
    AND LOAN.FFEL_DUP_ID = GA.SEP_LOAN_IND
    AND LOAN.SCH_BR_CODE = GA.ORIG_SCHL_CD
    AND STU.CURR_SSN = GA.STU_SSN
    AND STU.DOB = GA.DOB
    and stu.curr_fst = ga.stu_first_nam
  --and (plus_bor.curr_ssn is not distinct from ga.plus_brwr_ssn )

当我添加注释掉的行时，出现以下错误。

ORA-00908: missing NULL keyword
00908. 00000 -  "missing NULL keyword"
*Cause:    
*Action:

与 DB2 中的这种结构中的工作方式没有什么区别，但 Oracle 给我带来了问题。有什么建议吗？

如果我用=替换is not distinct from，我不会出错，但这在逻辑上并不相同。

如果两个值都null，则is not distinct from给出匹配项，而在这种情况下=不匹配。

Answer 1

在 Oracle 中模拟IS [ NOT ] DISTINCT FROM的最简单方法是使用 DECODE ：

-- a IS DISTINCT FROM b
DECODE(a, b, 1, 0) = 0
-- a IS NOT DISTINCT FROM b
DECODE(a, b, 1, 0) = 1

这就是您在使用jOOQ的SQL方言转换器时得到的结果。为此，一个小提琴：

WITH t (x) AS (
  SELECT 1    FROM dual UNION ALL
  SELECT 2    FROM dual UNION ALL
  SELECT null FROM dual
)
SELECT
  t1.x AS x1,
  t2.x AS x2,
  DECODE(t1.x, t2.x, 1, 0) AS not_distinct
FROM t t1, t t2
ORDER BY 1, 2

收益 率：

  X1 |   X2 | NOT_DISTINCT
-----+------+-------------
   1 |    1 |            1
   1 |    2 |            0
   1 | null |            0
   2 |    1 |            0
   2 |    2 |            1
   2 | null |            0
null |    1 |            0
null |    2 |            0
null | null |            1

Answer 2

您可以通过将NOT EXISTS与INTERSECT结合使用来模拟IS DISTINCT FROM：

plus_bor.curr_ssn IS DISTINCT FROM ga.plus_brwr_ssn
<=>
NOT EXISTS (SELECT plus_bor.curr_ssn FROM dual INTERSECT
            SELECT ga.plus_brwr_ssn FROM dual);

例：

WITH cte(a,b) AS (
    SELECT 1, NULL    FROM dual UNION ALL
    SELECT 1,2        FROM dual UNION ALL
    SELECT 1,1        FROM dual UNION ALL
    SELECT NULL, 1    FROM dual UNION ALL
    SELECT NULL, NULL FROM dual
)
SELECT *
FROM cte
WHERE NOT EXISTS (SELECT a FROM dual INTERSECT
                SELECT b FROM dual)

Rextester 演示

输出：

A       B
------------
1       NULL
1       2
NULL    1

<小时 />

在您的情况下，IS NOT DISTINCT FROM只是EXISTS：

plus_bor.curr_ssn IS NOT DISTINCT FROM ga.plus_brwr_ssn
<=>
EXISTS (SELECT plus_bor.curr_ssn FROM dual INTERSECT
        SELECT ga.plus_brwr_ssn FROM dual);

例：

WITH cte(a,b) AS (
    SELECT 1, NULL      FROM dual UNION ALL
    SELECT 1,2          FROM dual UNION ALL
    SELECT 1,1          FROM dual UNION ALL
    SELECT NULL, 1      FROM dual UNION ALL
    SELECT NULL, NULL   FROM dual
)
SELECT *
FROM cte
WHERE EXISTS (SELECT a FROM dual INTERSECT
              SELECT b FROM dual);

输出：

 A      B
 1      1
 NULL   NULL

Rextester 演示2

<小时 />

补遗

与评论中提出的COALESCE/NVL方法相比，这种方法有一个很大的优势。您不必考虑依赖于数据类型的默认中性值。

例如，如果列是数据类型DATE/INT/TEXT那么你必须写如下内容：

coalesce(col1,DATE '1900-01-01') = coalesce(col2,DATE '1900-01-01')
coalesce(col1, 0) = coalesce(col2, 0)
coalesce(col1, ' ') = coalesce(col2, ' ')

当然有轻微的碰撞机会。例如：

coalesce(col1, 0) = coalesce(col2, 0)
=>
col1 = NULL
col2 = 0
and we have incorrect match!!!