CODEWITHSUNDEEP
sqloracle-databasejoin
Qrom
Qrom

Reputation: 497

Oracle try second condition only if first one gave nothing in join

I'm looking in Oracle for a way to do something like if first condition has no match, do the second one. Let's get to an example.

I have this JOIN in a query :

SELECT A.COL1, A.COL2, B.COL1, B.COL2 
FROM A
FULL JOIN (SELECT COL1, COL2 FROM B)
ON B.COL1 = A.COL1
       OR B.COL2 = A.COL2

And you see, with this OR condition I can have two created joins for the same B object. And I don't want that. What I would like is something like :

Upvotes: 0

Views: 726

Answers (2)

MT0
MT0

Reputation: 168071

You need to include the negation of the condition B.COL1 = A.COL1 in the second filter. Naively, this is B.COL1 <> A.COL1 but this does not handle NULL values so you need to use B.COL1 <> A.COL1 OR B.COL1 IS NULL OR A.COL1 IS NULL like this:

SELECT A.COL1, A.COL2, B.COL1, B.COL2 
FROM   A
       FULL JOIN (SELECT COL1, COL2 FROM B)
       ON (  B.COL1 = A.COL1
          OR ( ( B.COL1 <> A.COL1 OR B.COL1 IS NULL OR A.COL1 IS NULL )
               AND B.COL2 = A.COL2
             )
          )

Upvotes: 1

jarlh
jarlh

Reputation: 44776

If B.COL1 = A.COL1 then return row. Or, if B.COL1 <> A.COL1 but B.COL2 = A.COL2 also return row.

SELECT A.COL1, A.COL2, B.COL1, B.COL2 
FROM A
FULL JOIN (SELECT COL1, COL2 FROM B) B
   ON (B.COL1 = A.COL1)
   OR (B.COL1 <> A.COL1 and B.COL2 = A.COL2)

Upvotes: 1

Related Questions