INNER And OUTER JOIN On Multiple Nullable Columns-CodePudding

The query we are currently testing gives doubtable results, where Inner JOIN LEFT OUTER JOIN != B Count.

The comparaison columns used in both tables are Nullable, but our goal is clear:

In case of INNER JOIN or intersection, Nulls should not be used for comparaison.
In case of LEFT OUTER JOIN, if both Bs are Nulls, should be included as not found in A, otherwise, we should compaire only the Not-Nulls.

Intesection

SELECT b.c1,b.c2
FROM B as b
Inner JOIN A as a
ON (( b.c1= a.c1 and not a.c1 is null)
  OR (b.c2=a.c2 and not a.c2 is null))

The previous code gives duplicate values when one of the columns is null!

In B not in A

SELECT b.c1,b.c2
FROM B as b
LEFT OUTER JOIN A as a
ON (( b.c1= a.c1 and not a.c1 is null)
      OR (b.c2=a.c2 and not a.c2 is null))
WHERE a.id IS NULL
-- filter duplicates
GROUP BY b.c1,b.c2

I guess the problem lies in the ON part where we join tables.

Thank you in advance

CodePudding user response：

You could run this with sub-queries rather than a join if you prefer.

SELECT b.c1,b.c2
FROM B as b
WHERE
b.c1 IN (SELECT c1 from a WHERE c1 IS NOT NULL)
OR (b.c2 IN (SELECT c2 from a WHERE c2 IS NOT NULL);