is not distinct from in join on clause oracle

Question

I have a query structured with the left outer join like so:

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 )

When I add the commented out line, I get the following error.

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

is not distinct from works fine in this structure in DB2, but Oracle is giving me issues. Any suggestions?

I get no errors if I replaced is not distinct from with a = but that isn't the same logically.

is not distinct from with give a match if both values are null, where as = would not match in this case.

Answer 1

The simplest way to emulate IS [ NOT ] DISTINCT FROM in Oracle is by using DECODE:

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

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

This is what you're getting when you're using jOOQ's SQL dialect translator. A dbfiddle for this:

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

Yields:

  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

You could emulate IS DISTINCT FROM by using NOT EXISTS combined with INTERSECT:

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);

Example:

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 Demo

Output:

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

---

And in your case IS NOT DISTINCT FROM is simply 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);

Example:

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);

Output:

 A      B
 1      1
 NULL   NULL

Rextester Demo2

---

ADDENDUM

This approach has one big advantage over COALESCE/NVL approach as proposed in comments. You don't have to think about default neutral value dependent on datatype.

For example if column is datatype DATE/INT/TEXT then you have to write something like:

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

There is of course slight chance of collision. For example:

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